IN Statement in Store Procedure

[sqlblind]

Hi Guys,

I have a simple store procedure which looks a bit like this:

@ProductCodes as varchar(25)

Select * from Market_Promotions
where Booths_ID in (@ProductCodes)

When I try to execute this and pass in a number of values I get an error message.

Can you please give me some guidance on the best approach to take using IN clause in a store procedure?

Cheers

 

[SQLScholar]

How are you passing in the "number of variables"?

You cant just pass in 'value1,value2,value3' else you will end up with

Select * from Market_Promotions
where Booths_ID in ('value1,value2,value3')

When actually you need

Select * from Market_Promotions
where Booths_ID in ('value1','value2','value3')

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

One of the easiest ways to resolve this is with a split function
(there are loads of these - example here

http://www.sqlservercentral.com/scripts/Miscellaneous/31913/)

You would then do something like this

Select * from Market_Promotions
INNER JOIN
fnDStringToTable(@ProductCodes,',') Codes on codes.values = Market_Promotions.Booths_ID

As i say there are loads of split functions (most return a table like this). So have a look round and find the best one for you. SQLservercentral have loads of these under the scripts section.

Hope this makes sense.

Dan


----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------