Using IN(parameter) with stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using IN(parameter) with stored procedures

Author

Topic

dave_idms
Starting Member

1 Post

Posted - 2007-10-11 : 12:12:04

When I use IN with a stored procedure like:

SELECT *
FROM Customers
WHERE State In('OH', 'IN', 'PA', 'FL', 'CA')

I succesfully retrieve all records cooresponding to the states I list in the comma delimited set.

My problem is I want to dynamically create the list at the business layer of my application.

When I pass in a parameter to my stored procedure like:

SELECT *
FROM Customers
WHERE State In(@paramStates)

I get no results.

Can someone help me out. Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-11 : 12:14:48

You need to use dynamic sql here:

declare @sql varchar(7000)
set @sql = 'SELECT * FROM Customers WHERE State In(' + @paramStates + ')')
exec(@sql)

Also read this alternative:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Need2CSharp
Starting Member

9 Posts

Posted - 2007-10-11 : 12:15:25

Hi Dave,

I had the same issue recently. Here is an outstanding article that offers several solutions:

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

Hope that helps!

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 15:32:15

"Here is an outstanding article that offers several solutions"

There are a few ideas here too:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-11 : 16:42:51

Do not use dynamic sql for something as simple and basic as this. Just create a simple User-Defined that returns rows from a CSV string.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources