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
(@ids nvarchar(100))
AS
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 
Using EXECUTE will give you a working solution, but it’s inferior to your original in-line SQL, which is precompiled and optimized by the database server (in addition to the benefit of sytnax error checking at compile-time).
Another way to write this query as in-line SQL involves the use of an array- convert your comma-delimited string to an array within your proc. I don’t know the syntax for SQL Sever, but in Postgres you do it like this:
SELECT * FROM myTable
WHERE myField = any (string_to_array(myParam, ‘,’));
where myParam is a varchar input parameter containing a comma-delimited list
Hi jhon thanks for your suggestion, i completely agree. Your one is a good solution.
Thanks