Can I pass a list of values as a parameter? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can I pass a list of values as a parameter?

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2012-12-13 : 11:57:05

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-13 : 12:24:24

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources