We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

passing multiple int parameter to stored procedure

LATurk
LATurk asked
on
Medium Priority
1,456 Views
Last Modified: 2007-12-19
Is there an easy way to pass multiple ints in as a parameter?  

For example, the where clause will be:  where id = 12345 or 23456 or 45678

I want it to read where id = @p1

Thanks

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thank you so much for your help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.