Stored Proc: Passing Values and Select Conditionals

[DrinkN1]

Multiple questions here, but all relate to the same SQL Statement I'm working on...

I'm creating a stored procedure that needs to accept 5 separate parameters:
ID1, ID2 and ID3 are all integer record IDs
ID4 and ID5 need to contain multiple integer ID's.

So, I need to be able to do a query such as:
select * from X where a=@ID1 and b=@ID2 and c=@ID3
and d in (@ID4) and e in (@ID5)

Quest #1: @ID2 can be a valid ID (postive integer) or 0. If the value = 0, then I need all the records returned, with no filter applied. How can I put a conditional in the select to say "if @ID2=y then select all where b=y, else select b > 0"

Question #2: How would I pass ID4 and ID5 over? Is this possible?

Thanks!

 

[BogdanMBM]

1. You can use an "if" statement and 2 "select"s depending on the value passed as @ID2.

2. From what I know, there may be a way to work around this by using table types for @ID4 and @ID5, not int. See BOL about this.
Another idea I have in mind is to pass as @ID4 and @ID5 not the actual list of IDs, but the select statement that retrives them. Then you can use dinamic SQL (EXEC ... bla, bla) to execute this):

If not @ID2 = 0
Begin
Exec (‘select * from X where a=’ + convert(varchar(20),@ID1) + ‘ and b=’ convert(varchar(20),@ID2) + ‘ and c=’ convert(varchar(20),@ID3) +
‘and d in (‘ + @ID4 + ‘) and e in (‘ + @ID5 ‘)’)
end
Else
Begin
Exec (‘select * from X where a=’ + convert(varchar (20),@ID1) + ‘ and b>0 and c=’ convert(varchar(20),@ID3) + ‘and d in (‘ + @ID4 + ‘) and e in (‘ + @ID5 ‘)’)
end
You can allways use a "case" statement if you wanna put all this in only one line...

Here, @ID4 is a varchar like this:
'SELECT ColumnName from Table Where Condition ble ble...'
The same for @ID5...

Hope I've been helpful,
Bogdan Muresan.

 

[SQLSister]

Passing a list of values to a Stored Procedure
faq183-3979