Skip to content
Advertisement

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

Advertisement

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;
    
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement