8

I just learned about the new sp_executesql stored procedure. I like the fact that there's a way to execute parameterized code from within SQL Server.

But, I'm wondering what the difference is between using the sp_executesql stored procedure when you don't have any parameters versus just calling EXEC directly. Also, is there a performance implication?

exec('select * from line_segment')
exec sp_executesql N'select * from line_segment'

Also, is there a difference between 2005 and 2008 or do they handle these the same?

11

sp_executesql supports parameterisation, whereas EXEC only accepts a string.

Only performance differences that may arise are due to the parameterisation i.e. a parameterised sp_executesql call is more likely to have a reusable cached plan. An EXEC call is likely to lead to lots of single use space wasters in the plan cache.

  • So, when sp_executesql is called with no parameters, it's just as efficient as EXEC? – Richard Aug 11 '11 at 18:11
  • If both produce the same execution plan then yes, the performance must be the same. You could argue that parameter sniffing might cause problems with sp_executesql but the causes and solutions would be the same as for stored procs. – Mark Storey-Smith Aug 11 '11 at 18:36
1

This helped me to crack the interview that's why I'm posting to help someone for the same.

enter image description here

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.