EXECUTE versus sp_executesql

There are times when an application must build a T-SQL statement dynamically before executing it on the server. In order to execute a dynamically created T-SQL statement, we can use either the EXECUTE command or the sp_executesql stored procedure. The sp_executesql procedure is the preferred method for executing dynamic T-SQL because it allows us to add parameter markers and thus increases the likelihood that the SQL Database Engine will be able to reuse the plan and avoid costly query compilations.

Here’s an example script from the AdventureWorks database that builds a dynamic T-SQL statement and executes it via the EXECUTE command:

DECLARE @sql nvarchar(MAX), @JobTitle nvarchar(50) = N'Sales Representative';
SET @sql = 'SELECT e.BusinessEntityID, p.FirstName, p.LastName
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = N''' + @JobTitle + &apos...