In this article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure.
Given below the Methods :
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to replace the parameter with single quote and create a dynamic query and execute it.
Given below is the Example Code and Script.
SCRIPT
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE
[dbo].[Get_StockHistory]
(@Id varchar(500))
AS
Begin
set @Id =
Replace(@Id, ',',''',''')
DECLARE @SqlQuery VARCHAR(MAX)
SET @ SqlQuery =
'SELECT Column1, Column2, Column3 FROM TabelName
WHERE Column1 IN (''' + @Id + ''') ORDER BY ItemId'
EXEC (@SqlQuery)
END
CODE
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE
[dbo].[Get_StockHistory]
(@ItemId varchar(500))
AS
Begin
declare @Itid
set @Itid='338,274,275,289,58,1227,322,323,324,325'
SET @Itid =
REPLACE(@id,',',''',''')
DECLARE @SqlQuery VARCHAR(MAX)
SET @ SqlQuery =
'SELECT ItemId,ItemName,Price,AvailableQty FROM ItemDetail
WHERE ItemId IN (''' + @Itid + ''') ORDER BY ItemId'
EXEC (@SqlQuery)
END
