IF/ELSE makes stored procedure not return a result set
- by Brendan Long
I have a stored procedure that needs to return something from one of two databases:
IF @x = 1
SELECT @y FROM Table_A
ELSE IF @x = 2
SELECT @y FROM Table_B
Either SELECT alone will return what I want, but adding the IF/ELSE makes it stop returning anything. I tried:
IF @x = 1
RETURN SELECT @y FROM Table_A
ELSE IF @x = 2
RETURN SELECT @y FROM Table_B
But that causes a syntax error.
The two options I see are both horrible:
Do a UNION and make sure that only one side has any results:
SELECT @y FROM Table_A WHERE @x = 1
UNION
SELECT @y FROM Table_B WHERE @x = 2
Create a temporary table to store one row in, and create and delete it every time I run this procedure (lots).
Neither solution is elegant, and I assume they would both be horrible for performance (unless MS SQL is smart enough not to search the tables when the WHERE class is always false).
Is there anything else I can do? Is option 1 not as bad as I think?
