EXECUTE (or EXEC) vs sp_executesql

–> EXECUTE:
As per MS BOL EXECUTE executes a command string or character string within a TSQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar data type.

–> sp_executesql:
As per MS BOL sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain embedded parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be prefixed with N.

–> Main difference performance wise: sp_executesql is generally preferred over EXEC() when executing dynamic T-SQL. sp_executesql works by creating a stored procedure using the specified query, then calling it using the supplied parameters. Unlike EXEC(), sp_executesql provides a mechanism that allows you to parameterize dynamic T-SQL and encourage plan reuse. A dynamic query that is executed using sp_executesql has a much better chance of avoiding unnecessary compilation and resource costs than one ran using EXEC().

Let’s check the difference with a simple example using [AdventureWorks] database’s [Person].[Contact] table:

USE [AdventureWorks]
GO

DECLARE @str NVARCHAR(1000)
DECLARE @fName NVARCHAR(20)
DECLARE @ePromo INT

SET @fName = N'James'
SET @ePromo = 1

--// Using EXECUTE or EXEC
SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName = ''' + @fName + '''
AND EmailPromotion = ' + CAST(@ePromo as NVARCHAR(20))

PRINT @str

EXEC (@str)
GO

--// Using sp_executesql
DECLARE @str NVARCHAR(1000)
DECLARE @fName NVARCHAR(20)
DECLARE @ePromo INT

DECLARE @paramList NVARCHAR(500)
SET @paramList = N'@fNameParam NVARCHAR(20), @ePromoParam INT'

SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName = @fNameParam
AND EmailPromotion = @ePromoParam'

SET @fName = N'James'
SET @ePromo = 1
EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo

-- When the match is LIKE
SET @str = N'
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion
FROM Person.Contact
WHERE FirstName LIKE ''%'' + @fNameParam + ''%''
AND EmailPromotion = @ePromoParam'

SET @fName = N'James'
SET @ePromo = 0
EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo
GO

Security Note:
Before you call EXECUTE or sp_executesql with a character string, validate the character string. Never execute a command constructed from user input that has not been validated. For more information, see SQL Injection.