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
— 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
insert into #TempTable values (left(@ids, charindex(‘,’, @ids+’,’)-1))
set @ids = stuff(@ids, 1, charindex(‘,’, @ids+’,’), ”)
— Insert statements for procedure here
select * from mytable where id in (select ID from #TempTable)