Just Code‎ > ‎

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

posted Aug 29, 2012, 5:37 AM by Peter Henell   [ updated Aug 29, 2012, 5:42 AM ]
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
Comments