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.