Learn T-SQL Querying

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 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 ...