Stored Proc: Parameter As Filter with Multiple Values

[MomentSurfer]

Greetings everyone,

I wish to seek some advice and perhaps gain some insight towards how other people are approaching a similar task.

I frequently use stored procedures to provide "list data" for my applications. The stored procedures usually have parameters to allow the aplication with a way of filter the data, server side. The filters are optional. If no parameter is specified all data is returned. If a parameter is specified only the matching data is returned. Here is an example of my typical stored proc for this:

....
@CustomerIDFilter int
......

SELECT Customer.CustomerID, Customer.CompanyName
FROM Customer
WHERE
@CustomerIDFilter IS NULL OR Customer.CustomerID = @CustomerIDFilter

This approach has been working great, and I really enjoy how clean and flexible the structure is.

My challenge is I sometimes encounter situations where the application needs to specify a filter but for *MULTIPLE* records.

e.g. WHERE CustomerID = 1 AND CustomerID = 5

If this were the case, I would use an nvarchar parameter called @CustomerIDWhereFilter and then have the application pass in the WHERE clause. I would then use another nvarchar variable to dynamically generate my entire SELECT / WHERE clause. This approach is far less cleaner because it deals with a whole lot of string concatenation.

Do you have any suggestions for how I could easily accomodate multiple values in a more clean and elegant manner?

Any insight you can provide would be greatly appreciated.

Thanks for your time.

David

 

[vongrunt]

Split string into temp table/table @variable, then join on that table... check FAQ section for this forum.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[jbenson001]

e.g. WHERE CustomerID = 1 AND CustomerID = 5

is this a typo and should be OR?

 

[vongrunt]

Not if customer has some split-personality issues (a la Dr Jekyll)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[MomentSurfer]

Thank you for your prompt response. FAQ #faq183-3132 was extremely helpful! So elegant.

David

PS: My customers do not have split personalities... it was in fact a type-o.

 

[vongrunt]

Er... not this faq... see faq183-3979 and faq183-5207

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]