CODEWITHSUNDEEP
sqlsql-servert-sql
DaveB
DaveB

Reputation: 9530

How Can I Check If Any IF Block Executed In SQL Server

In my SQL Server stored procedure, I have a number of IF blocks.

IF @CurrentStatus IN (1, 4) AND @RoleID IN ('ADMN', 'PMGR', 'SMGR', 'DMGR', 'DESI', 'DERO')
    BEGIN
            -- SELECT query...
    END

IF @CurrentStatus = 2 AND @RoleID IN('ADMN')
    BEGIN
            -- SELECT query...
    END

There are several more IF blocks like these. When none of the queries are executed because the conditions of the IF are not met, there will be no data returned. I have a query I want to return as the default if there is no other data being returned. How can I check if any data is being returned at the end of my stored procedure?

I tried checking if any queries had been executed with IF @@ROWCOUNT = 0 but when one of the IF blocks executed, I would get 2 result sets.

Upvotes: 0

Views: 192

Answers (1)

George Mastros
George Mastros

Reputation: 24498

If I understand correctly, you could use If / Else If / Else, like this...

IF @CurrentStatus IN (1, 4) AND @RoleID IN ('ADMN', 'PMGR', 'SMGR', 'DMGR', 'DESI', 'DERO')
    BEGIN
            -- SELECT query...
    END

Else IF @CurrentStatus = 2 AND @RoleID IN('ADMN')
    BEGIN
            -- SELECT query...
    END

Else
    BEGIN
       -- Default query
    END

Upvotes: 2

Related Questions