passing multiple int parameter to stored procedure

pass the ints as a comma delimeted parameter

e.g.

exec  yourproc '123,456,789'

then inside

either use dynamic sql (not preferred)

declare @sqlstr varchar(8000)
set @sqlstr = 'Select ....  where id in (' + @parm1 + ')'
exec (@SQLSTR)


or decode the string into a temp table/table variable
and join to it..

e.g.

declare @i int
declare @next int
set @i = 1

select convert(int,0) as [id] into #temp where 0=1

while charindex(',',@parm1,@i) > 0
begin
      Set @next = charindex(',',@parm1,@i)
      Insert into #temp select convert(int,substring(@parm1,@i,@next-1))
      set @i=@next+1
end

insert into #temp select convert(int,substring(@parm1,@i,datalength(@parm1) +1 - @i))

select a.*
from xyz as a
 inner join #temp as t
 on a.[id] = t.[id]

 

Inside the store procdure...

create procedure pname(declare @x as varchar(100))
declare @q as nvarchar(500)
--set @x = '(0,1,2,3,4)'  /* this is an eg: assign your input values like these, @x is the input parameter */
set @q = 'select * from tblname where field in' + @x
EXECUTE sp_executesql @q

I'm having problems with this.  If I run the stored procedure below in Query Analyzer passing two numbers like this:

myProcedure '159563,159562'
it works.

When I add one more number to the string as in this:

myProcedure '159563,159562,159564'

I get the following error:

Server: Msg 245, Level 16, State 1, Procedure Loribtreport, Line 13
Syntax error converting the varchar value '159562,159564' to a column of data type int.


CREATE PROCEDURE myProcedure (@p1 varchar(500)) as

declare @i int
declare @next int
set @i = 1

Select convert(int,0) as [id] into #temp where 0=1

while charindex(',',@p1,@i) > 0

begin
      Set @next = charindex(',',@p1,@i)
      Insert into #temp select convert(int,substring(@p1,@i,@next - 1))
      Set @i=@next + 1
end

insert into #temp select convert(int,substring(@p1, @i, datalength(@p1) + 1 - @i))

select * from #temp

Thank you so much for your help.