sp_executesql

sp_executesql
[sp_executesql in comparison to Execute]

If you want to have the flexibility of dynamic SQL, but the persistence of a stored query plan, consider using sp_executesql in your stored procedures instead of EXEC. The syntax for sp_executesql is as follows:

sp_executesql @SQL_commands, @parameter_definitions, param1,...paramN

sp_executesql operates just as the EXEC statement with regard to the scope of names, permissions, and database context. However, sp_executesql is more efficient when executing the same SQL commands repeatedly, and the only change is the values of the parameters. Because the SQL statement remains constant and only the parameters change, SQL Server is more likely to reuse the execution plan generated for the first execution and simply substitute the new parameter values. This saves the overhead from having to compile a new execution plan each time.
[Via:Microsoft SQL Server 2000 Unleashed By Ray Rankins, Paul Jensen, Paul Bertucci]

The sp_executesql SP provides a second method of executing dynamic sql. When used correctly, it is safer than the simple EXECUTE method for concatenating strings and executing them. Like EXECUTE, sp_executesql takes a string constant or variable as a sql statement to execute. Unlike EXECUTE, the sql statement parameter must be an nchar or nvarchar.

The sp_executesql procedure offers a distinct advantage over the EXECUTE method; you can specify your parameters seperately from the SQL statement. When you specify the parameters separately instead of concatenating them into one large string, SQL server passes the parameters to sp_executesql seperately. SQL server then substitutes the value of the parameters in the parameterized SQL statement. Because the parameter values are not concatenated into the SQL statement, sp_executesql protects against SQL injection attacks. sp_executesql parameterization also improves query execution plan cache reuse, which helps with performance.

A limitation to this approach is that you cannot use the parameters in your SQL statement in place of table, column or other object names.

Example of sp_executesql

DECLARE @product_name NVARCHAR(50) = N' Mountain%';
DECLARE @sql_stmt NVARCHAR(128) = N'SELECT ProductID, Name ' +
N'FROM Production.Product ' +
N'WHERE Name LIKE @name';
EXECUTE sp_executesql @sql_stmt,
N'@name NVARCHAR(50)',
@name = @product_name;

[Via:Pro T-SQL 2008 Programmer's Guide By Michael Coles]

The key difference in the syntax between the EXEC() and sp_executeSQL is that EXEC() will allow you to concatenate literals and variables to build up a string expression, while sp_executesql needs a string constant or a single string variable.

See more on difference between EXCUTE & EXECUTE().

sp_executesql must be passed a Unicode string.