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.
| Author |
Topic |
|
fezz
Starting Member
12 Posts |
 Posted - 2011-01-20 : 10:43:51
|
| I want to be able to pass the values to my stored procedure which as part of the query looks for them using an IN statement which is fine but i cant seem to pass multiple values to the sp in the first place.values = 1,2,3 exec some_sp_name @val = '1' works but only for 1 item.Whats the best way of doing this?ThanksPete |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-20 : 10:46:05
|
| Pass them as a csv string.Then in the SP either use dynamic sql or (preferably) parse the csv string into a table and use that (I would use a cte to do it).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-01-20 : 12:55:39
|
| The best/preferred method is the use a Table Valued Parameter. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-01-20 : 14:08:09
|
quote:
Whats the best way of doing this?
The worst, most non-relational way to do this is tp write a parser in T-SQL. The most proprietary way is a table type declaration. For the painful details of this read: http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/ http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/ --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
Passing multiple values to a sp