[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.
No comments:
Post a Comment