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 we to add parameter markers and thus increases the likelihood that SQL Server will be able to reuse the plan and avoid costly query compilations.
Here's a sample 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 AS e
INNER JOIN Person.Person AS p ON p.BusinessEntityID...