IF/ELSE makes stored procedure not return a result set
Posted
by Brendan Long
on Stack Overflow
See other posts from Stack Overflow
or by Brendan Long
Published on 2010-06-02T19:02:51Z
Indexed on
2010/06/02
19:04 UTC
Read the original article
Hit count: 187
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
UNIONand 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?
© Stack Overflow or respective owner
