Use sp_executesql or EXEC ?

There are many different thoughts out there on the use of sp_executesql vs EXEC.  Personally, I use sp_executesql rather than EXEC(), because it has better security, and it can have better performance.

First, this is because sp_executesql allows us to use parameterized statements.  EXEC/EXECUTE does not.  The parameterized statements do not expose us to the risk of SQL Injection.  Hence, the better security. 

Secondly, if we use sp_executesql to execute our statements that are called again and again, the Optimizer will reuse the execution plan.  And, we can achieve better performance from the cached query plans.

See this quick sp_executesql example, using AdventureWorks2012:


DECLARE
  @sqlCommand nvarchar(1000),
  @ColumnList varchar(75),
  @CountryRegionCode nvarchar(3)

SET @ColumnList = 'StateProvinceID, StateProvinceCode,CountryRegionCode,[Name]'
SET @CountryRegionCode = 'US'
SET @sqlCommand = 'SELECT ' + @ColumnList + ' FROM Person.StateProvince WHERE CountryRegionCode = @CountryRegionCode'

EXECUTE sp_executesql @sqlCommand, N'@CountryRegionCode nvarchar(3)', @CountryRegionCode = @CountryRegionCode

 

More information:  http://technet.microsoft.com/en-us/library/ms188001.aspx