10

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?

1

2 Answers 2

13

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.

2
  • So, when sp_executesql is called with no parameters, it's just as efficient as EXEC?
    – Richard
    Aug 11, 2011 at 18:11
  • 1
    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. Aug 11, 2011 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!

1
  • 2. sp_executesql Is save for sql injection. 3. White space is irrelevant. 4.any plan is cached, but parameterised plans are reused. Please do not insert text as image. It is not searchable, not copied for paste, not editable Oct 31, 2021 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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