Stored Procs, parameters and IN Clause
-
This question relates to MS-SQL 2000 I would like to write a query such as.. SELECT * FROM Users WHERE id IN @UserIDs Where @UserIDs is a list of integers passed into the stored proc. Is this possible? The list of ID's is generated by a user, so I cant use a sub query to get the required list of ID's. If it is possible, what type do I define the parameter as? Thanks Stephen
-
This question relates to MS-SQL 2000 I would like to write a query such as.. SELECT * FROM Users WHERE id IN @UserIDs Where @UserIDs is a list of integers passed into the stored proc. Is this possible? The list of ID's is generated by a user, so I cant use a sub query to get the required list of ID's. If it is possible, what type do I define the parameter as? Thanks Stephen
There is a way, but I don't recommend it. For one thing, you lose out on one of the key reasons for using a stored proc...the pre-compilation by SQL Server. Here's how you do it.... create procedure dbo.usp_Test ( @ids varchar(100) ) as declare @sql as varchar(255) set @sql = 'select * from table where tableid in (' + @ids + ')' exec(@sql) GO then you pass the ids in as a comma delimited string... exec usp_Test '1000, 1001, 1010'