Problem with sp_executesql

[Hexonx]

I've found an interesting difference in behavior between executing a dynamic SQL string and using sp_executesql. I've illustrated the difference in code below, using the example for sp_executesql from BOL. I need to execute a query with a filter like, "WHERE Id IN (1,2)". Executing a fully concatenated string runs fine, but there's no query plan reuse. Using sp_executesql in order to leverage query plan reuse results in this error:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '32,35' to a column of data type tinyint.

Any ideas?


DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable


-- Level IDs to include
DECLARE @vcVariable VARCHAR(100)
SET @vcVariable = '32,35'

-- Succeeds
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl IN (' + @vcVariable + ')'
EXECUTE(@SQLString)

-- Fails
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl IN (@level)'
SET @ParmDefinition = N'@level varchar(100)'
/* Execute the string with the first parameter value. */
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @vcVariable

 

[JamesLean]

Yes, I would expect that behaviour. In your example that fails, you're basically passing the comma-delimited list into the SQL string directly just like a normal parameter. If you could do that there would be no need to use the dynamic SQL. Even when using sp_executesql you still need to concatenate the list of values into the string - that is the key to being able to use this technique:

SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl IN (' + @vcVariable + ')'

--both of these should work
EXECUTE(@SQLString)
EXECUTE sp_executesql @SQLString

For more ideas on how to pass in a list like this have a look at this FAQ:

Passing a list of values to a Stored Procedure
faq183-3979

--James

 

[Hexonx]

Thanks, James. Your FAQ was helpful, as well as the ref to the MSDN article.

After posting, I was using Profiler to see how the execution plan caching was being affected. I could see the query that was being executed by sp_executesql. It looked something like this: ... WHERE job_lvl IN (@level). No wonder it didn't work!