using in with SP

Question

Post reply

using in with SP

ssurekha2000

SSCommitted

Points: 1860
More actions
August 12, 2013 at 4:59 am

#276869

i have a SP which has a param id as int
and the where claus ein SP had where id=@ID
but now instead of a single vlaue i get id as multiple values
so i need IN operator
redefined ID as varchar
i used where id IN (@ID)
and when executing SP as EXEC usp_itemdeatils '45,72' i get error
Conversion failed when converting the varchar value '45,72' to data type int.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

Hi

It's not working because the WHERE clause becomes:

WHERE id IN ('45,72')

which obviously won't work as you have a varchar value there. One way you could get around it is to use dynamic SQL and add the "IN string" into the query and then execute it.

You may also be able to it by putting the values into a temporary table or table variable and using a join in the query.

Duncan

ohadpick Grasshopper Points: 23 More actions · Answer 2

Hi,

You can use this solution:

http://mitchelsellers.com/blogs/2008/08/08/using-the-in-clause-with-stored-procedures.aspx

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Using dynamic code might cause problems with sql injection. You could try to use the 8K splitter [/url]to split your string.

You could end up with something like this:

SELECT *

FROM MyTable t

WHERE EXISTS( SELECT *

FROM dbo.DelimitedSplit8k(@ID,',') s

WHERE t.id = s.item)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

ohadpick (8/12/2013)

Hi,

You can use this solution:

http://mitchelsellers.com/blogs/2008/08/08/using-the-in-clause-with-stored-procedures.aspx%5B/quote%5D

You should check out the link that Luis pointed you too, or the one in my signature about splitting strings. It is way faster than a while loop and it is safe from sql injection.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chrissy321 SSCoach Points: 15766 More actions · Answer 5

You can also use Table-Valued Parameters

http://www.sommarskog.se/arrays-in-sql-2008.html