SQL Server Performance Forum – Threads Archive
passing a comma delimited list to an SP
Hello,<br /><br />I’m trying to pass a comma delimited list from a ColdFusion page to a stored procedure. <br /><br />I’m running the exec like this:<br /><br />exec get_Dealer_Search_PRI_Detail ‘#StartDate#’, ‘#EndDate#’, #ID#, ‘#IDType#’, #OID#, ‘#cBrandID#'<br /><br />where #cBrandID# is a comma delimited list.<br /><br />I’m declaring my variable like this (along with the others):<br /><br />(@cBrandID varchar(50))<br /><br />and using the variable in the SQL like this:<br /><br />AND prf.Brand_id_int in (@cBrandID)<br /><br />but I get an error saying I can’t convert a varchar to an int. <br /><br />Is there any way to do this?<br /><br />Thanks, [<img src=’/community/emoticons/emotion-1.gif’ alt=’![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Check Convert funtion in BOL.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Didn’t work. I tried:<br /><br />AND prf.Brand_id_int in (convert (int,@cBrandID))<br /><br />but still got this error:<br /><br />Syntax error converting the varchar value ‘1,2’ to a column of data type int.<br /><br />The reason this is happening is because it’s not a single digit that I’m trying to load into the parentheses, it’s a comma delimited list.<br /><br />Any other ideas?<br /><br />Thanks for your help, [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Would you give an example of what do you expect prf.Brand_id_int have? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Write a function to split the Comma Saperated List into table of values. Write Code like this… AND prf.Brand_id_int in dbo.Fn_SplitCommaSaperatedString(@cBrandID) HTH.
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Hi Luis,<br /><br />Thanks for your response. @cBrandID will be any combinations of the numbers 1-5, in single quotes. So it could be just a ‘1’ or a ‘2’, but it also could be ‘1,2’ or ‘2,5’ or ‘1,2,3,4’ etc. etc.<br /><br />I just tried converting the database column to a varchar like this:<br /><br />AND convert(varchar(50),prf.Brand_id_int) in (@cBrandID)<br /><br />and I got no error, but I got no results.<br /><br />Please help, [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
Hi Gaurav, You might have to work with me a bit here, I’m a bit of a neo at T-SQL. What is the code to get the list into the table? Thanks, Peter
I don’t have the exact code but I cn give u some directions. See syntax for creating functions, and then see explaination of CHARINDEX, LEFT, RIGHT. This should give you a fair idea of writing a function to split the comma saperated value and return as a table. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Another option is to create dynamic SQL like …
…
SELECT @Select_Statement = @Select_Statement + "AND prf.Brand_id_int in (" + @cBrandID + ")"
….
….
SP_EXECUTESQL(@Select_Statement) Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Please read this
http://www.algonet.se/~sommar/arrays-in-sql.html
and
http://www.algonet.se/~sommar/dynamic_sql.html
Frank
http://www.insidesql.de
http://www.familienzirkus.de
All is good. [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
a bit late, but you can find a more efficient solution following this link
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1092 Bambola.
That’s very interesting.
Maybe Joe should make a short article of this. Would be easier to reference to, IMHO. Frank
http://www.insidesql.de
http://www.familienzirkus.de
You could do it like this:<br /><br /><pre>WHERE CHARINDEX(‘,’ + CAST(prf.Brand_id_int as varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) + ‘,’,’,’ + case @cBrandID when ” then CAST(prf.Brand_id_int as varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) else @cBrandID end + ‘,’)>0</pre><br /><br />If you send an empty list it will return all posts…<br /><br />Good luck!
]]>