How to pass a list of strings as a parameter in a stored procedure in SQL?

Like for example if I have a stored procedure proc_aggregation that takes two parameters @Prod_Desc of varchar data type and another parameter @Prod_Code as a list of strings (Eg : @Prod_Code = ('12012', '12011', '12014')).

CodePudding user response：

You will have to use table valued parameters

1. Define new type as follows

   CREATE TYPE Prod_Code AS TABLE ( ProductCode varchar );
   

2. then use this type in your stored procedure

    create procedure [dbo].[proc_aggregation]
    @Prod_Code Prod_Code READONLY,
    @Prod_Desc varchar (30)
    as
    ......
   

3. Now before calling the stored procedure fill the table

    declare @PC Prod_Code;
    insert @PC VALUES ('12012'), ('12011'), ('12014')
   

4. Now Call the sp like this

    EXEC dbo.proc_aggregation @PC, @Prod_Desc;
   

CodePudding user response：

You can pass this data as varchar(max) parameter and then parse this string using string_split

Declare @Prod_Code VarChar(max) = '12012,12011,12014'

Select value As [Values]
From String_Split(@Prod_Code, ',')

or pass this data as a json array and parse this text using OPENJSON

Declare @Prod_Code VarChar(max) = '[12012, 12011, 12014]'

Select value As [Values]
From OPENJSON(@Prod_Code)

CodePudding user response：

As an interesting fact, let'us compare performances of the different solutions... STRING, JSON, XML.

SET NOCOUNT ON;
GO
CREATE TABLE #test_list (id int identity primary key, style varchar(16), VAL VARCHAR(64), DT DATETIME2)
GO
-- JSON test
INSERT INTO #test_list VALUES ('JSON', '[12012, 12011, 12014]', SYSDATETIME());
GO
Select value As [Values]
From OPENJSON((SELECT VAL FROM #test_list WHERE style = 'JSON'));
GO 10000

-- STRING test
INSERT INTO #test_list VALUES ('STRING', '12012,12011,12014', SYSDATETIME());
GO
Select value As [Values]
From String_Split((SELECT VAL FROM #test_list WHERE style = 'STRING'), ',')
GO 10000

-- XML test
INSERT INTO #test_list VALUES ('XML', '<i v="12012" /><i v="12011" /><i v="12014" />', SYSDATETIME());
GO
WITH TX AS 
(SELECT CAST(VAL AS xml) AS x
 FROM #test_list AS i
 WHERE style = 'XML')
Select y.value('(@v)[1]', 'varchar(16)')
From TX
     CROSS APPLY x.nodes('/i') AS T(y);
GO 10000
-- final
INSERT INTO #test_list VALUES ('END', NULL, SYSDATETIME());

Executing the test without any dataset returned... (SSMS menu : Query/Options/Results/Grid/Ignore results after execution...)

Computing the execution time, is done by this query :

SELECT begins.style, 
       DATEDIFF(millisecond, begins.DT, ends.DT) AS DurationMS
FROM   #test_list AS begins
       JOIN #test_list AS ends ON begins.id = ends.id - 1
ORDER  BY 2;

It is clear that string solution wins :

style             DurationMS
---------------- -----------
STRING                  2977
JSON                    3358
XML                     4242