SQL Server Performance Forum – Threads Archive

passing a comma delimited list to an SP

Hello, I’m trying to pass a comma delimited list from a ColdFusion page to a stored procedure. I’m running the exec like this: exec get_Dealer_Search_PRI_Detail ‘#StartDate#’, ‘#EndDate#’, #ID#, ‘#IDType#’, #OID#, ‘#cBrandID#' where #cBrandID# is a comma delimited list. I’m declaring my variable like this (along with the others): (@cBrandID varchar(50)) and using the variable in the SQL like this: AND prf.Brand_id_int in (@cBrandID) but I get an error saying I can’t convert a varchar to an int. Is there any way to do this? Thanks, [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Peter

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: AND prf.Brand_id_int in (convert (int,@cBrandID)) but still got this error: Syntax error converting the varchar value ‘1,2’ to a column of data type int. 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. Any other ideas? Thanks for your help, [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Peter

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, 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. I just tried converting the database column to a varchar like this: AND convert(varchar(50),prf.Brand_id_int) in (@cBrandID) and I got no error, but I got no results. Please help, [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Peter

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=’

‘ />] I solved it by inserting the list into a table and joining the prf table onto this temp table. Here is the code I used to create the table: DECLARE @pos int CREATE TABLE #list (val varchar(10)) SET @cBrandID = @cBrandID + ‘,' WHILE CHARINDEX(‘,’, @cBrandID) > 0 BEGIN SET @pos = CHARINDEX(‘,’, @cBrandID) INSERT #list VALUES (LTRIM(RTRIM(LEFT(@cBrandID, @pos – 1)))) SET @cBrandID = STUFF(@cBrandID, 1, @pos, ”) END And here’s the code for the join. join #list li (nolock) on li.val = prf.brand_id_int Bascally what this amounts to is I can declare @cBrandID as a varchar, insert each value between commas into a table, and then join on this table. Thanks to gaurav_bindlish and FrankKalis for their help. The pages Frank sent over were especially useful. Why didn’t those pages appear on yahoo! when I did a search on them? It doesn’t matter. Thanks again, Peter Swanson

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: <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> If you send an empty list it will return all posts… Good luck!

]]>