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

Dynamic Variables and Stored Procedures

Question:
I need to pass the @TheVar variable to the stored procedure's IN statement but I keep getting zero records. If I type this statement into the SQL...
WHERE Name IN ('John', 'Frank', 'Tom')
the statement works fine; 20 records are returned.
-----------------------------------
EXECUTE the_proc "'John', 'Frank', 'Tom'"
-----------------------------------

DECLARE PROCEDURE the_proc 
@TheVar nvarchar(40) 
AS
SELECT COUNT(FieldName) 
FROM Clients 
WHERE Name IN (@TheVar)

-----------------------------
Do you have any ideas? I use MS SQL Server 7.0 SP2.

Answer:
If you want to use variables as part of your SQL statement you need to modify it to call the EXECUTE statement. I created a Clients table with a single Name field of varchar(255) with records for John, Frank, and Tom. Then I created a stored procedure (similar to yours) that uses the input variable to build the IN clause like this:

CREATE PROCEDURE GetClients
@TheVar varchar(255) 
AS
EXEC ('SELECT COUNT(Name) 
FROM Clients 
WHERE Name IN (' + @TheVar + ')')
As you can see, it builds the SELECT statement using the variable and then the EXEC statement runs it.

The syntax to call this procedure is:

GetClients "'John', 'Tom', 'Frank'"

----------- 
3

(1 row(s) affected)
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