Passing list of values to Stored Procedure using Cold Fusion

[tkav8]

I am using Cold Fusion to call a stored procedure and pass a list of values to the "Where" clause (ie. Where ID IN (1,2,3...)). The procedure errors because when the comma delimited list is passed, the stored procedure thinks that the delimiting commas are seperating additional parameters that are being passed. I cannot use quotes around the parameter, because the list contains integers. I am calling the procedure in a SQL Server 2000 database. I can loop through the list in the code, and pass each ID individually, but this could certainly become a performance issue. Any help would be appreciated.

 

[tlbroadbent]

You can pass a list as a delimited string but must either parse the string or create a dynamic SQL statement in the SQL Stored procedure. See thread183-292139 for some examples.

You would pass the delimeted string like this... Note the quotes around the entire string but not around each item in the list.


exec spYourProc '1,5,6,9' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tkav8]

Terry, thanks very much. I was passing the list with single quotes around the whole thing, but not building sql statement dynamically. I did this, and it works fine. Thanks for the help, and sorry about the similar post on the in clause, but I couldn't find it Friday in a search. Have a great day.
Trey