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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-17 : 07:38:02
|
| slipknot_IW writes "How do i use IN statement in Stored Procedure? I am passing 1 to nth numer of item numbers as a variables in it...I have tried to use @items varcharDeclare x varcharset @x = 'Select * from item where itemnumber in (' + items + ')'Exec (@x)Doesn't work.Thanks in advance" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-17 : 07:51:17
|
| missing @set @x = 'Select * from item where itemnumber in (' + @items + ')'What is in @items==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vipinspg
Starting Member
12 Posts |
Posted - 2005-05-17 : 09:47:34
|
| If you want to avoid Dynamic SQL for the above problem, you can use a table variable like this.-- Append an extra "," at the end of @items. Sample value for @items is '1,2,' DECLARE @v_itemtab TABLE(itemnumber int) DECLARE @v_position int,@v_oldposition int,@v_itemnumber int SET @v_position = 0 SET @v_oldposition = 0 SET @v_itemnumber = 0 IF(@items <> '') BEGIN SELECT @v_position = CHARINDEX(',', @items, @v_position) WHILE(@v_position > 0) BEGIN SET @v_itemnumber = SUBSTRING(@items, @v_oldposition + 1, @v_position - @v_oldposition -1) SET @v_itemnumber = CONVERT(INT,@v_itemnumber) INSERT INTO @v_itemtab(itemnumber ) VALUES(@v_itemnumber) SET @v_oldposition = @v_position SELECT @v_position = CHARINDEX(',', @items, @v_position + 1) END END SELECT * from item WHERE itemnumber IN (SELECT itemnumber FROM @v_itemtab). |
|
|
|
|
|
|
|
|
How do I use IN statement in Stored procedure?