devxlogo

Dynamic Variables and Stored Procedures

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) 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 GetClients@TheVar 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)

See also  How to Avoid Money Transfer Scams  
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist