TSQL - Snippet example of sp_executeSql to use parameters in dynamic query

Post date: Aug 29, 2012 12:37:39 PM

To avoid filling your query plan cache with rubbish you should parameterize your queries, even your dynamic SQL queries.

It is easy to use parameters so it should not be any reason to allow your query plan cache to be used up by worthless plans that will never be reused. If possible at least!

Press Ctrl+Shift+M to fill in the template parameters.

Oh, and change the query to what you want it to do. This is just a small example of using parameters in dynamic sql.

sp_executesql @stmt = N'select * from master..sysprocesses where last_Batch > <ParamName, varchar, @pararama>' ,@params = N'<ParamName, varchar, @pararama> datetime' ,<ParamName, varchar, @pararama> = '<ParamValue, varchar, 2012-01-01>'

Another example using a variable

declare @date datetime = '2012-01-02'exec sp_executesql @stmt = N'select * from master..sysprocesses where last_Batch &gt; @lastBatch' ,@params = N'@lastBatch datetime', @lastBatch = @date