Archive

Posts Tagged ‘list’

Pass a comma separated list to a stored procedure

October 24, 2012 Leave a comment

Sometimes we need to pass a list or an array to a stored procedure to retrieve some results like in the query below:

select * from mytable where id in (1,2)

But doing this using a Stored Procedure will cause the query to be rendered as follows (select * from mytable where id in (‘1,2’)) which will cause this error: Conversion failed when converting the varchar value ‘1,2’ to data type int.

So To pass an array of ids (numbers) to a stored procedure, you can do it using the below:

Create PROCEDURE [dbo].[spGetData]
— Add the parameters for the stored procedure here
@ids nvarchar(MAX)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #TempTable(ID int)
while len(@ids) > 0
begin
insert into #TempTable values (left(@ids, charindex(‘,’, @ids+’,’)-1))
set @ids = stuff(@ids, 1, charindex(‘,’, @ids+’,’), ”)
end
— Insert statements for procedure here
select * from mytable where id in (select ID from #TempTable)
END

Advertisements