SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can I pass a list of values as a parameter?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaveBF
Yak Posting Veteran

87 Posts

Posted - 12/13/2012 :  11:57:05  Show Profile  Reply with Quote
Is it possible to do something like this:

exec sp_executesql N'select age from person where

age IN @agelist',

N'@agelist nvarchar(25),',@agelist=N'(40,41,42,43)'

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/13/2012 :  12:24:24  Show Profile  Reply with Quote
No, it is not possible to do that except in the trivial case where you have just one entry in the comma-separated list.

Within the stored proc, you should split the comma-separated string into a virtual table and join to that table. There are string splitter functions available - an especially good one is here in Fig. 21: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Copy that function, run it to install it and use it like in the examples on that page.

An alterative approach is to do something like the following:
WHERE
   ','+@agelist+',' LIKE '%,'+CAST(age AS VARCHAR(8))+',%'
Splitting the comma-separated list is a better approach from a performance perspective.


There are other ways of passing arrays to a stored procedure - see Sommarskog's article here: http://www.sommarskog.se/arrays-in-sql.html
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000