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