Executing dynamic SQL statements in stored procedures

Executing dynamic SQL statements in stored procedures
MSSQL offers two ways: exec and sp_executesql.
Usually the latter has the advantage of providing an input/output interface, and exec has no
The biggest advantage of sp_executesql is the ability to reuse execution plans, greatly improving execution performance, so use sp_executesql as much as possible, and it's more flexible

1 Use of EXEC

There are two uses of exec: executing a stored procedure, and performing a dynamic batch processing
Only one string variable is allowed in the exec brackets, but multiple variables can be concatenated, for example:
XEC (' SELECT TOP (' + CAST (@TopCount as VARCHAR) + ') * from ' +
QUOTENAME (@TableName) + ' ORDER by ORDERID DESC ');
This will cause the compiler to make an error and compile the
But you can do this: EXEC (@[email protected][email protected]);

The best way to do this is to construct the code into a variable, and then use that variable as an input parameter named EXEC.

The disadvantage of exec is that it cannot execute a batch that contains a variable

For example:

  DECLARE @TableName VARCHAR,@Sql NVARCHAR (MAX), @OrderID INT;   ' Orders ' ;   10251 ;   '  '+quotename (@TableName) + 'WHERE OrderID = @OrderID ORDER by OrderID DESC c17> '  EXEC (@sql);

When using exec, if you want to access variables, you must concatenate the variable contents into a dynamically constructed code string, such as:

SET @sql = ' SELECT * from ' +quotename (@TableName) + ' WHERE OrderID = ' +cast (@OrderID as VARCHAR) + ' ORDER by OrderID DESC '

exec does not support output parameters in addition to input parameters in dynamic batching, and by default, EXEC returns the output of the query to the caller,

However: if the output is to be returned to a variable in the call batch, it is cumbersome to insert the output into a target table using the INSERT EXEC syntax, and then assign the value to the variable after getting the values from the table

DECLARE @TableName VARCHAR ( -), @sql NVARCHAR (max), @OrderID INT, @sql2 NVARCHAR (max); SET @TableName='Orders'; SET @OrderID=10251; SET @sql='SELECT * from'+quotename (@TableName) +'WHERE OrderID ='+cast (@OrderID as VARCHAR ( -)) +'ORDER by ORDERID DESC'EXEC sp_executesql @sql

2 sp_executesql

sp_executesql Support input parameters also support output parameters, this function can create a query string with parameters
Syntax: code block, Parameter declaration section, Parameter Assignment section

EXEC sp_executesql

@stmt = <statement>,--Similar to a stored procedure body

@params = <params>,--similar to the stored Procedure Parameters section, declaring parameter types

<params assignment>--similar to a stored procedure call, to assign values to parameters, parameter values to be corresponding to the order of the parameters, you can also be assigned a value by specifying the parameter value for the parameter
@stmt: is a dynamic batch of inputs that can introduce input parameters or output parameters, just like the body of a stored procedure, except that it is dynamic and stored procedures are static and can be used in stored procedures sp_executesql
The @params parameter is similar to the stored procedure header that defines the input/output parameters, and is actually exactly the same as the syntax of the stored procedure header;
@<params assignment> is similar to the exec part of calling stored procedures.

In fact @stmt, @params can be omitted, then exec sp_executesql syntax can be written in the following form:

EXEC sp_executesql    <statement>,   <params>,   <params assignment>

Another powerful feature of Sq_executesql is that you can use the output parameter to return a value for a variable that calls a batch, which avoids returning data with a temporary table, and the syntax for defining and using an output parameter is similar to a stored procedure and requires that the OUTPUT clause be specified at the time of declaration

Identifying string constants with the letter N prefix

Summarize:
1 using EXEC sp_executesql efficiency is higher than exec, the same type of statement, just compile once, and exec executes several times to compile several times
2 when constructing the WHERE clause of dynamic SQL, which is the conditional clause, EXEC cannot use variables to take up, it needs to convert the variable to a string and then splice with dynamic SQL, which may cause SQL injection problems
Example: SET @sql = ' SELECT * from ' +quotename (@TableName) +
' WHERE OrderID = ' +cast (@OrderID as VARCHAR) + ' ORDER by OrderID DESC '

and exec sp_executesql: You can use variables to position, and then give this parameter value of the way to construct dynamic SQL, so as to avoid the SQL injection problem

SET @sql = ' SELECT * from ' [e-mail protected] + ' WHERE OrderID = @OID ORDER by OrderID DESC '

3 Whether it is exec or exec sp_executesql, if you want to dynamically parameterize column names and table names, you cannot use column names and table names for positioning.

Executing dynamic SQL statements in stored procedures