gotchaModerate
Difference between EXEC() and sp_executesql with no parameters?
Viewed 0 times
withanddifferencesp_executesqlbetweenexecparameters
Problem
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?
Also, is there a difference between 2005 and 2008 or do they handle these the same?
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#4559, answer score: 13
Revisions (0)
No revisions yet.