I would like to execute a SELECT statement in a stored procedure, but before executing, I have to know if it will have rows. If not, I have to jump to a specified Label, and set an output. My current method works just fine, but I will need more of these in several procedures, and I was wondering if I could do this without code repetition (and without Ctrl+C, Ctrl+V) (It’s just a sample code, not my real code)
x
IF EXISTS (
SELECT *
FROM sample s
INNER JOIN sample2 s2 ON s.Id = s2.sId
WHERE s2.number = @input
)
BEGIN
INSERT INTO @Temp
SELECT *
FROM sample s
INNER JOIN sample2 s2 ON s.Id = s2.sId
WHERE s2.number = @input
END
ELSE
BEGIN
SET @noresult = 1;
GOTO label;
END
Thank you for helping!
Advertisement
Answer
Why not just load the table and check afterwards?
INSERT INTO @Temp
SELECT *
FROM sample s JOIN
sample2 s2 ON s.Id = s2.sId
WHERE s2.number = @input;
IF NOT EXISTS (SELECT 1 FROM @temp)
BEGIN
SET @noresult = 1;
GOTO label;
END;
If @noresult
is the same for all the tables, you can have just one if
after loading a bunch of temp tables.