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
Postgres Query to Check a String Is a Number
How to Limit Results of a Left Join
How to Make a Join of 3 Tables in Jpa
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Sql - Sum of Positive and Negative Numbers Using Subquery
Alternative to Row_Number() to Get Row Position
Job for Mysqld.Service Failed See "Systemctl Status Mysqld.Service"
Oracle Pl/Sql String Compare Issue
Sql Query for Increase Item Value Price for Multiple Item
Sql Server:How to Test If a String Has Only Digit Characters
Auto Increment the Column Value by 1 Conditionally in Select Query in SQL
Sql 0 Results for 'Not In' and 'In' When Row Does Exist
How to Select Rows With No Matching Entry in Another Table
How to Combine First Name, Middle Name and Last Name in SQL Server
What's the Best Way to Capitalise the First Letter of Each Word in a String in SQL Server
Remove Numbers from String SQL Server