sp_executesql

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  January 6, 2011 at 8:34 am

  #1270138

  Junglee_George (1/6/2011)

  ---

  Also

  The primary difference between both is that Exec statement is Unparameterised whereas sp_executeSql is Parameterised.

  I'd change that statement to "sp_executesql can be parameterized" because queries run through sp_executesql are not automatically so. You must code them correctly.

  If we use sp_executeSql, the cached plan would be created only once and would be reused 'n' number of times for ‘n’ number of parameters.

  So this would have better performance.

  Using Exec, 2 separate execution plans will be created.

  But when we are using sp_executesql, the execution plan will be created only once and will be reused for the 2 parameters and hence the time would be saved in this.

  Also, you can get code reuse in EXEC too. But the parameters are that the code has to be identical. Changing the value of a comparison operator inside the string that composes the TSQL will prevent reuse, but if no changes are made, the already compiled plan can be used again. Again, sp_executesql will only guarantee code reuse if it's coded correctly to do so.

  ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software