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?
Best Answer
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.