IF/ELSE makes stored procedure not return a result set

Question

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:

1. 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

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?

Answer 1

are you sure that @x is 1 or 2?

declare @x int

set @x = 1

IF @x = 1
    SELECT 'syscolumns',* FROM syscolumns
ELSE IF @x = 2
    SELECT 'sysobjects',* from sysobjects
    else
    select 'not 1 or 2'

BTW what is this line supposed to do

SELECT @y FROM Table_B

it will just return the same variable @y for however many rows you have in TableB

based on you comment, 0 means success, the proc did not error out. Don't use a return statement, use an output parameter instead

based on your second comment you can use this, note this will only work in a proc since inline sql cannot use the return statement

declare @y int,@x int
select @y = -500


IF @x = 1
    SELECT  @y = y FROM Table_A
ELSE IF @x = 2
    SELECT  @y = y FROM Table_B

return @y

Answer 2

Some ideas:

First, why the "else"?

IF @x = 1 
    SELECT y FROM Table_A 
IF @x = 2 
    SELECT y FROM Table_B 

should do what you want without the worries of nesting, block declaration (BEGIN/END), and the like.

Second, any chance that @x is null? If it were, neither query would run.

Third, for a "scary message", add something like this after the above two lines:

IF isnull(@x, 0) not in (1,2)
    RAISERROR('Data invalid, reformatting C:\', 20, 1)

(Ok, you could have a better error message, but that'd sure scare me...)