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) ASSELECT 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 [email protected] varchar(255) ASEXEC ('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)
