devxlogo

Passing a CSV List Within an IN Statement

Passing a CSV List Within an IN Statement

Question:
I have a CSV list I am passing through as a parameter in a stored procedure.

-------------------------------Example of Region list:1,2,3,4,5,6,7,8,9,10CREATE PROCEDURE sp_getReport   @Regionlist 
varchar(100) AS .......Select * from tblRegionwhere Region_ID IN (@regionlist)---------------------------------

The varchar treats the list as one block of data. How can I change my code/syntax to read each value of the list separately within the IN statement?

Answer:
When you pass variables that are part of the statement in a stored procedure, you need to use the EXEC(UTE) statement. Here’s the syntax to call the stored procedure and modify the current stored procedure to take the regions list as a parameter (the only difference is I called the column RegionID instead of Region_ID):

sp_getreport '1,2,3,4,5,6,7,8,9,10'CREATE PROCEDURE sp_getReport   @regionlist varchar(100) ASEXEC ('Select * from tblRegion where RegionID IN 
(' + @regionlist +')')

devx-admin

Share the Post:
Show More