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

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')).

Answer

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;