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 12, 2000

SQL Server Stored Procedures

Question:
I have a stored procedure that takes a costcenterid (int) as an input parameter. The user wants to search on an unspecified number of costcenters each time. I use the 'IN' clause to find the cost center. But how do I pass these unspecified number of costcenter IDs to my stored procedure?

Answer:
You can create an IN clause with a variable but you must use the EXECUTE statement. For example, let's use a simple query that has an IN clause from the Northwind database:

SELECT * 
FROM Categories
WHERE CategoryName IN ('Beverages', 'Condiments')
This returns two categories. Here's the syntax that uses a variable for the IN clause:
DECLARE @InString varchar(255)
SET @InString = "'Beverages', 'Condiments'"

EXEC ( 'SELECT * 
FROM Categories
WHERE CategoryName IN ('+ @InString +')')
This syntax returns the same results as the previous query. In a stored procedure the variable would be passed as a parameter and used in the same way. Since you're passing all the cost centers as a single string, the number can vary from query to query.
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