SQL SERVER – Passing multiple values through one parameter in a stored procedure

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.

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.

Query:



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

Example:


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