I faced problem when i tried to send parameter in a store procedure which parameter is located in the ‘IN’ of the query. first i written a query ‘select * from users where in IN(1,2,4)’ — worrls nicely, then i tried to use store procedure to parametarize data i have written a store procedure look like that
create proc getuser( @ids as varchar(50) ) AS select * from users where in IN(@ids) — not working. why will it work you are giving parameter as varchar but you are give id as integer, what you want to do change parameter type? it is no a solution too as you will send all id once through the parameter.
So the solution is
create proc proc_test
declare @sql as nvarchar(1000)
set @sql = N’SELECT * FROM JoinPool WHERE ID IN (‘ + @ids + ‘)’
EXECUTE sp_executesql @sql
exec proc_test ‘1,2,3,5’ — will return result 🙂