How to Pass Multiple Values to Single Parameter in Stored Procedure

How to pass multiple values to single parameter in stored procedure

This can not be done easily. There's no way to make an NVARCHAR parameter take "more than one value". What I've done before is - as you do already - make the parameter value like a list with comma-separated values. Then, split this string up into its parts in the stored procedure.

Splitting up can be done using string functions. Add every part to a temporary table. Pseudo-code for this could be:

CREATE TABLE #TempTable (ID INT)
WHILE LEN(@PortfolioID) > 0
BEGIN
IF NOT <@PortfolioID contains Comma>
BEGIN
INSERT INTO #TempTable VALUES CAST(@PortfolioID as INT)
SET @PortfolioID = ''
END ELSE
BEGIN
INSERT INTO #Temptable VALUES CAST(<Part until next comma> AS INT)
SET @PortfolioID = <Everything after the next comma>
END
END

Then, change your condition to

WHERE PortfolioId IN (SELECT ID FROM #TempTable)

EDIT

You may be interested in the documentation for multi value parameters in SSRS, which states:

You can define a multivalue parameter for any report parameter that
you create. However, if you want to pass multiple parameter values
back to a data source by using the query, the following requirements
must be satisfied:

The data source must be SQL Server, Oracle, Analysis Services, SAP BI
NetWeaver, or Hyperion Essbase.

The data source cannot be a stored procedure. Reporting Services does
not support passing a multivalue parameter array to a stored
procedure.

The query must use an IN clause to specify the parameter.

This I found here.

Pass multiple values to stored procedure

You can refer this article. There are 5 ways in which you can achieve it:

Method #1 – Passing a CSV: list of strings as a parameter to a (N)VARCHAR datatype parameter, then splitting/parsing it inside the SP
or UDF, check here.

Method #2 – Passing an XML: string as an XML datatype parameter. We will need to parse the XML inside the SP, check here.

Method #3 – Using a temp table: inside an SP which is created outside just before its execution. Here there is no need to pass any
parameter with the SP, check here.

Method #4 – Using TVPs: With SQL Server 2008 and above you can create TVPs or Table Valued Parameters and declare them by using
user-defined table types. These TVPs can then be used to send multiple
rows of data to SPs or UDFs, without creating a temp table or multiple
parameters, check here.

Method #5 – Passing a JSON string: as a NVARCHAR datatype parameter. We will need to parse the JSON inside the SP, check here.

For example the method 1 example from the linked source:

-- As always I will use the AdventureWorks database<img width="16" height="16" class="wp-smiley emoji" draggable="false" alt=":)" src="https://s1.wp.com/wp-content/mu-plugins/wpcom-smileys/simple-smile.svg" style="height: 1em; max-height: 1em;">
USE [AdventureWorks2012]
GO

-- Create an SP with NVARCHAR(MAX) parameter:
CREATE PROCEDURE uspGetPersonDetailsCSV (
@persons NVARCHAR(MAX)
)
AS
BEGIN
--DECLARE @persons NVARCHAR(MAX)
--SET @persons = 'Charles,Jade,Jim,Luke,Ken'

SELECT T.C.value('.', 'NVARCHAR(100)') AS [Name]
INTO #tblPersons
FROM (SELECT CAST ('<Name>' + REPLACE(@persons, ',', '</Name><Name>') + '</Name>' AS XML) AS [Names]) AS A
CROSS APPLY Names.nodes('/Name') as T(C)

SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, ModifiedDate
FROM [Person].[Person] PER
WHERE EXISTS (SELECT Name FROM #tblPersons tmp WHERE tmp.Name = PER.FirstName)
ORDER BY FirstName, LastName

DROP TABLE #tblPersons
END
GO

-- No execute this SP by passing a list of values comma separated as a single string:
EXEC uspGetPersonDetailsCSV 'Charles,Jade,Jim,Luke,Ken'
GO
-- Check the output, objective achieved<img width="16" height="16" class="wp-smiley emoji" draggable="false" alt=":)" src="https://s1.wp.com/wp-content/mu-plugins/wpcom-smileys/simple-smile.svg" style="height: 1em; max-height: 1em;">

-- Final Cleanup
DROP PROCEDURE uspGetPersonDetailsCSV
GO

How to pass two values to a single parameter in SQL Server stored procedure?

You can try this code to extract table from your parameter:

declare @sql nvarchar(max)=
'abc,def,ghi,jkl'
set @sql = replace(@sql,',','''),(''')
set @sql = 'select *
from (values ('''+@sql+''')
) a ( Value )
'
PRINT(@sql)
exec sp_executesql @sql

Then use execution result with IN-condition. You can configure this part with any delimiter.

Multiple values in one parameter in a stored procedure

First you need to define type like following

CREATE TYPE ProductList AS TABLE
(
ProductId INT
)
GO

Then create your procedure like following

ALTER PROCEDURE USP_TEST_PROC (@OrderId INT,@Produt_Id_List ProductList READONLY)
AS
BEGIN

DECLARE @OrderDetails TABLE(OrderId INT, ProductID INT,UnitPrice DECIMAL(18,2),Quantity INT, Discount DECIMAL(18,2))

DECLARE @Products TABLE(ProductID INT,UnitPrice DECIMAL(18,2),Quantity INT, Discount DECIMAL(18,2))

INSERT INTO @OrderDetails (OrderId, ProductID,UnitPrice,Quantity, Discount)
SELECT @OrderId, ProductID,UnitPrice,Quantity,Discount FROM @Products WHERE ProductID IN (SELECT ProductId FROM @Produt_Id_List)

SELECT * FROM @OrderDetails
END

Then prepare table variable to put values like following

DECLARE @PList ProductList;
INSERT @PList VALUES (1),(2),(3)

Finally call procedure

EXEC USP_TEST_PROC 100,@PList 

Thanks

How to pass multiple values to a stored procedure

Use a CASE statement, so if it's "ALL" you can pass back the Department field. If it's anything else pass the value you sent in:

Where Department = CASE @input WHEN 'ALL' Then Department ELSE @input END


Related Topics



Leave a reply



Submit