Skip to content
Advertisement

MSSQL Check if a SELECT statement will have rows, if yes, then execute the same SELECT statement

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement