Pass multiple values to stored procedure

Question

What is the correct way to run the following in SQL Server?

Execute myStore @dept='dept1',@dept='dept2'

or

Execute myStore @dept in ('dept1','dept2')

Answer 1

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