Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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