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 |
|
coderdude
Starting Member
24 Posts |
 Posted - 2003-07-30 : 18:21:37
|
| I need to write a stored procedure to accept a variable number of parameters as input. All of these parameters are primary keys (integers) for a table and there could be anywhere from one to 500 of them). I need to write the query to return a recordset of rows that match these primary keys.ie.. SELECT * FROM Table WHERE ID IN (10, 11, 12, 14)The only way I could figure to accomplish this would be to have one varchar(8000) parameter and pass in a comma delimited string of the primary keys. This seems to work just fine, however, I have to build my query as a string and then execute it like so...SET @SQL = 'SELECT * FROM Table WHERE ID IN (' + @PrimaryKeys + ')'EXEC(@SQL)I really don't want to build the query as a string because it is not as efficient and hard to read/maintain (the actual query is MUCH longer than my example).So my question is... How can I write this query to where I don't need to build the query as a string? One thought that came to mind would be create a temp table from the string values and then do a join, however, I'm not sure how to parse the values out of the string into a new table (may be even less efficient).Please help!Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-30 : 18:41:39
|
| This has been covered in an article:[url]http://www.sqlteam.com/item.asp?ItemID=11499[/url]Tara |
 |
|
|
coderdude
Starting Member
24 Posts |
Posted - 2003-07-30 : 18:48:06
|
| Thank you! Thank you! Thank you! |
|
|
|
|
|
|
Variable number of parameters