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 |
|
label
Posting Yak Master
197 Posts |
 Posted - 2003-04-08 : 21:21:53
|
I have the following statement in a Stored Proc:quote:
Declare Cursid cursor for select stockid from aims.dbo.vehicle where [year]=@year and make=@make and model=@model and Stockid in (select stockid from aims.dbo.Stockdamage where prioritysequence in (1, 2) and damagecode in (select damagecode from aims.dbo.RFDamageCode where [description] in (@damage)))
The problem I have is that the @damage variable could be one word like "Front" or it could be many words like "Front, Rear, Side". When I pass in a string like 'Front, Rear, Side' it obviously reads that as one value because the comma's are included in the string I'm passing into the Stored Proc. How can I pass in an array/string that could be one or many variables and have it work in the above select statement? Thanks!Edited by - label on 04/08/2003 21:22:48 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-04-08 : 23:42:00
|
| You can use several options:1. Dynamic SQL, which you execute using EXEC or sp_executesql, this will concatenate with the IN clause properly.2. A possibly better solution, this idea exists somewhere in a SQLTeam article: Create a temp table (or table variable), and fill it up with the @Damage values by parsing the string variable. This temp table will act like an array, and change the IN clause to IN(SELECT Damage FROM #TempDamage) or (SELECT Damage FROM @TblDamage)Sarah Berger MCSD |
 |
|
|
label
Posting Yak Master
197 Posts |
Posted - 2003-04-09 : 08:26:49
|
quote:
You can use several options:1. Dynamic SQL, which you execute using EXEC or sp_executesql, this will concatenate with the IN clause properly.2. A possibly better solution, this idea exists somewhere in a SQLTeam article: Create a temp table (or table variable), and fill it up with the @Damage values by parsing the string variable. This temp table will act like an array, and change the IN clause to IN(SELECT Damage FROM #TempDamage) or (SELECT Damage FROM @TblDamage)
Yeah, I had thought of both those options last night and decided to do generally what you're describing in point 2 with some modifcations.I guess my big question is whether or not you can pass an array of values through a string to a Stored Proc and then split out the values for use....Edited by - label on 04/09/2003 08:27:49 |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2003-04-09 : 08:44:52
|
| I think this is the article Sarah mentioned[url]http://www.sqlteam.com/item.asp?ItemID=637[/url]Andy |
|
|
|
|
|
|
|
|
Stored Proc question