Browse DevX
Sign up for e-mail newsletters from DevX

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



Building the Right Environment to Support AI, Machine Learning and Deep Learning

SQL Server Stored Procedures

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?

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:

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'"

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.



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