There are 2 methods of executing dynamic SQL :
  • Exec which executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure
  • sp_executesql which executes a SQL statement or batch that can be reused many times, or that has been built dynamically
Example SQL Statements - both returning the same results:

  • Exec ('Select * from Items')
  • DECLARE @strQuery AS NVARCHAR(50)
    SET @strQuery = 'Select * from Items'
    EXEC sp_executesql @strQuery

As you can see sp_executesql demands a parameter to be sent rather than just a string. This leads to improved security and improved performance because the SQL statement itself remains constant and only the parameter values change which means that the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

In addition, when writing a parameterized query, the additional parameters can be specified separately which again adds to the security, see the following which is based on the example on MSDN:
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @TitleDefinition NVARCHAR(50)

/* Build the SQL string once.*/
SET @SQLString =N'SELECT * FROM ItemsWHERE Title like ''%'' + @title + ''%'''
SET @ParmDefinition = N'@title nvarchar(50)'

* Execute the string with the first parameter value. */
SET @TitleDefinition = 'S'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @title = @TitleDefinition

/* Execute the same string with the second parameter value. */
SET @TitleDefinition = 'P'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @title = @TitleDefinition

So, if you have an option, use sp_executesql instead of exec