When creating a dynamic SQL statement in SQL Server 2000 and up avoid using the exec() function. There is a system stored procedure that works better called sp_executesql(). Here is a link to Microsoft's documentation: (SQL 2005) http://msdn.microsoft.com/en-us/library/ms188001(SQL.90).aspx (SQL 2000)
http://msdn.microsoft.com/en-us/library/aa933299%28SQL.80%29.aspx
At the time I was working with SQL Server, one of reasons why sp_executesql was preferred over exec() was performance (beside it can be parametrized). Which means that it predates sql server 2005 and comes from sql 2k era :)
ReplyDeleteJust found nice blog on this subject: http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/