Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 16, 2000

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,10

CREATE PROCEDURE sp_getReport   @Regionlist 
varchar(100) AS ....... Select * from tblRegion where 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) 
AS

EXEC ('Select * from tblRegion where RegionID IN 
(' + @regionlist +')')
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date