Querying different table based on a parameter

Question

I have a stored procedure that I would like to query either the production or the "work in progress" table, based on the parameter I am passing in. I could write two separate stored procedures, but I thought this was worth a try.

something along the lines of:

create procedure getUserDetails
    @userID int,
    @prod varchar(5)
as 
    begin
        select * from
            if (@prod = 'true')
            Begin
                userprod_table
            else
                userwip_table
            end 
    where something = 'something'
END

Is this at all possible? I wouldn't want to write 2 SP that are almost identical :-/

Not really related, but [Using SELECT * in production Code](http://stackoverflow.com/a/3180435/1048425) is **NEVER** a good idea. Do your two tables have the same columns? — GarethD, Mar 11 '13 at 14:55

score 1 · Accepted Answer · answered Mar 11 '13 at 14:46

1

Why not use a simple if(@prod = 'true') statement like below:

if (@prod = 'true')
begin
    select * from userprod_table where something = 'something'
end  else
begin
    select * from userwip_table where something = 'something'
end

answered Mar 11 '13 at 14:46

Kaf

33,101
7
58
78

score 0 · Answer 2 · answered Mar 11 '13 at 14:57

0

You could use a CTE so that your main query isn't repeated

with usertable as
(
    select * from userprod_table where 1 = @flag
    union
    select * from userwip_table where 0 = @flag
)
select ... from usertable ...

answered Mar 11 '13 at 14:57

Phil

42,255
9
100
100

Querying different table based on a parameter

2 Answers2

Linked