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)
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.