T-SQL run-time generated statements

run-time build statement

　　1. Execute the dynamic command with execute

　　　the Execute command can execute stored procedures, functions, and dynamic string commands. Note This statement acts as if the first statement in the batch is "Execute stored procedure" when the batch is described earlier, you can omit the keyword "execute".

Grammar:

EXEC | EXECUTE  @string_variable| [N] '  'name'  ] [;]

Parameter description:

EXEC: Is the shorthand for execute, both of which can be used.

@string_variable: The name of a local variable, which can be any char, varchar, nchar, or nvarchar data type, including (max) data type. You can encapsulate T-SQL code in a local variable to be executed.

[N] ' tsql_string ': a constant string that can make any nvarchar or varchar data type. If n is included, the string is interpreted as a nvarchar data type. If it is not a dynamically generated string command, it can be directly executed if it is written directly as a constant string.

[As {LOGIN | User} = ' name ']:login Specifies the context of execution as a login, so its execution scope is server-level, user specifies that the context for execution is users, so its execution scope is database level.

Note: Execute may cause SQL injection attacks in use, that is, SQL statements that go beyond the user's own permissions may be executed so that the contents of the string can be checked when the dynamic command string is generated. By specifying the execution context, use the [as {LOGIN | The syntax form of USER} = ' name '], which qualifies the execution environment of the EXECUTE statement to ensure security.

　　2. Execute dynamic command with sp_executesql

sp_executesql is a system stored procedure that functions roughly the same as execute, unlike the ability to support parameter substitution. The parameter substitution feature is not supported by execute.

Function:

Executes a T-SQL statement or batch that can be reused or generated dynamically, and can contain embedded parameters. The sp_executesql is the same as execute on the batch, TEMP variable scope, and database context.

Grammar:

      [] stmt [{, [@params =] N ' @parameter_na Me data_type [[PUT] [,... n]'} {,[]'  value1'[,... n]  }]

Parameter description:

[@stmt =] stmt: A Unicode string containing a T-SQL statement or batch, which must be a Unicode constant or variable that can be implicitly converted to ntext, but cannot be an expression. The use of string constants must be prefixed with N. For example, the Unicode constant n ' sp_who ' is valid. However, the string constant ' sp_who ' is not valid. The size of the string is limited only by the available database server memory.

[@params =] N ' @parameter_name data_type[,... n] ': The string defined by all parameters embedded in the stmt. The string must be a Unicode constant or variable that can be implicitly converted to ntext. Each parameter definition consists of a parameter name and a data type. Each parameter specified in stmt must be defined in @params. If the T-SQL statement or batch in stmt does not contain parameters. You do not need @params. The default value for this parameter is null. N is a placeholder that represents the definition of an additional parameter.

[@params1 =] ' value1 ': The value of the first parameter defined in the parameter string. This value can be used to make a constant or variable. You must supply a parameter value for each parameter contained in the stmt. These values are not required if the T-SQL statements or batches in stmt have no parameters.

Output: Indicates that the parameter is an out parameter.

N: Placeholder for additional parameters. These values can only be constants or variables. cannot be an expression.

code example:

Declare @sql_str nvarchar( $)Declare @Id intSet @Id =  theSet @sql_str = 'SELECT * from person where Id =' + Convert(varchar,@Id)Executesp_executesql@sql_str

　　3. Parameter substitution

Sp_excutesql and execute compare, their greatest special thought is the parameter substitution, and the EXECUTE statement does not support the function. Because execute does not support the inclusion of parameters in the executed statement, the SQL Server engine will need to recompile execution even if the following two functions are fully consistent, with only a different value being entered.

Select  from where 275 Select  from where 276　　　　

Conversely, if you use sp_executesql to execute, we can write execution code as follows, because only the substitution of parameter values, so the engine only need to compile once.

　　　　　　 Declare @intvariable int; Declare @sqlstring nvarchar( -); Declare @parmdefinition nvarchar( -); Set @stringsql =N'SELECT * from person where Id = @pId'          --first-time assignment execution 　　　　　　 Set @parmdefinition = 275 　　　　　　 EXECUTEsp_executesql@SQLString,@parmdefinition,@pId = @intvariable; Set @parmdefinition = 275          --Second Assignment Execution 　　　　　　 EXECUTEsp_executesql@SQLString,@parmdefinition,@pId = @intvariable;

In contrast to execute, sp_executesql executes a dynamically generated string command with a bit of the following:

　　　　　 1. High efficiency of parameter substitution

Just like the SQL statement above, SQL Server only needs to compile once for operations that differ only in parameters. And if you use Execute,sqlserver to compile two times.

As a consequence of the change in string literals, execute affects the functionality of the SQL Server query optimizer to match the new T-SQL string to an existing execution plan, while the actual text of the sp_executesql T-SQL is unchanged between two executions. So the query optimizer is able to match the T-SQL statements in the second execution to the execution plan generated at the first execution. Therefore, SQL Server does not have to compile the second statement.

Execute is regenerated every time, and sp_executesql only needs to be generated once.

String conversion, execute must convert the parameter value (non-character or Unicode value) to a character or Unicode format each time it executes. Integer parameters are specified in their own format. Conversion to Unicode is not required.

　　　　　　2, the implementation plan reuse brings the high efficiency.

Use Sp_execute to reuse the execution plan of SQL Server. You can use sp_executesql instead of stored procedures when you execute T-SQL statements more than once and change only the parameter values that are provided to T-SQL statements. Because the T-SQL statement itself remains the same, only the parameter values change, so the SQL Server query optimizer might reuse the execution plan that was generated the first time it was executed.

4, using the output parameters of the sp_executesql

Parameters can be used when executing with sp_executesql. There are two types of parameters, one is the input parameter and one is the output parameter.

The input parameter is to bring the external value into the string command, and the output parameter is to return the result of the string execution to the outside, usually in a temporary variable.

　　　　1, the definition of output parameters

When you define an output parameter, you can define the parameters by using the Outputs keyword or (out)

Syntax format:

[@params =] N'@parameter_name data_type OUTPUT}　　　　　　　　　　

　　　　2. Get the value of the output parameter

A common way to get the value of an output parameter is in the Sp_execute syntax, as in the following form.

= 　　the value of parameter 1 ... OUTPUT

delivery of data between statements

　　1. Data transfer between T-SQL statements

(1), local variables to pass data

A local variable is like a common store of data inside a batch, so a statement inside a batch can reference the defined variable data by the name of the local variable.

　　　(2), parameter transfer data

Parameters are objects that pass data between a stored procedure and a batch or script that executes the stored procedure. Parameters can be either an input parameter or an output parameter.

The name and form of the parameter are exactly the same as the local variables, and the type of the parameter needs to be explained. The difference is that a local variable is a means of passing data within a batch, and a parameter is a means of passing data across two code snippets or objects.

Error Handling

　　　(1), SQL Server database engine error

1. Query system error message

SQL Server stores system customizations (message_id <= 50000) and user-defined (message_id>50000) error messages in the system view sys.messages of each database.

2. Severity level of system error information

The resulting system error messages are categorized into different severity levels. The severity level is represented by a number, and the smaller the number indicates the lower the severity level. Conversely, the higher the severity. A high-severity error indicates that the problem needs to be resolved as soon as possible.

　　　(2), using Try...catch to find errors

Try...catch Structure begin try to execute the T - SQL code, once the error is passed to the catch block for processing End Try begin The code that catch retrieves and handles error messages End catch normal execution of T - SQL statements

Try: Where the try block is a T-SQL snippet that is contained between the begin try and end try, in which case an error is passed to the catch block and the code following the catch block is executed directly if there is no error.

The Catch:catch block is a T-SQL code snippet that is contained between the begin catch and end catch to retrieve and process the error information in the try block.

　　　(3) System functions that trap errors

1, Error_number ()

Returns the ID number of the error corresponding to the message_id field in the Sys.messages system view.

2, Error_line ()

Returns the number of statement lines in the T-SQL code that occurred incorrectly.

3, Error_message ()

Returns the text of the message that will be returned to the application. The text includes values provided for all replaceable parameters, such as length, object name, or time. Corresponds to the text field in the Sys.messages system view.

4, Error_procedure ()

Returns the name of the stored procedure or trigger where the error occurred. If no error occurs in the stored procedure or trigger, the function returns NULL.

5, Error_severity ()

Returns the severity level of the error. Corresponds to the Severity field in the Sys.messages system view.

6, Error_state ()

Return status

Example:

beginTrySelect 1/0EndTrybeginCatchSelecterror_number () as ' Number', Error_line () as ' Line', Error_message () as 'message', error_severity () as 'severity', Error_state () as ' State'EndCatch

Output Result:

　　　　　

(4), with @ @ERROR catch the error of the previous statement

T-SQL also provides a simple system function @ @ERROR to catch the error of the previous statement. If the previous statement executed successfully. @ @ERROR The system function returns 0, and if the previous statement generates an error, the @ @ERROR returns the error number.

At the completion of each statement, the @ @ERROR will change.

For example:

Select 1 / 0 Select *  from where = @ @error  and = 2052　　　　　　

Results

　　　　　　

(5), with RAISERROR feedback error

Function:

The generated SQL Server engine error or warning information (obtained from the sys.messages system view) is fed back into the application. Sys.messages the information that is defined by SQL Server itself in the system view, the value of its message_id column is less than or equal to 5000.

Returns a custom message that the user created using stored procedure sp_addmessage (stored in System view sys.messages, whose message_id is greater than 50000).

Differences from print Statements:

The print statement is a statement provided by T-SQL for feedback, and the print statement can only feed back the value of a string or string expression.

In addition to the function of the print statement, the RAISERROR statement supports string substitution functions like the C-language secondary printf function. This allows you to define the type and location of the data you want to replace in the string, and automatically replace the contents of the string when you export.

Grammar:

　　　　　　RAISERROR | | @local_variable     [] [with option [,... N]]

Parameter description:

MSG_ID: The error message number (message_id) stored in the Sys.messages system view. If the error message is customized by the user using the as_addmessage system stored procedure, the error number should be greater than 50000. If msg_id is not specified, an error message with an error number of 50000 is returned.

MSG_STR: User-defined message, MSG_STR is a string with an optional embedded conversion specification. Each conversion specification defines the values in the parameter list. How to format and place it in a field on the conversion specification location in MSG_STR, the format of the conversion specification is as follows:%[[flag][width][.precision][{h|1}]]type.

@local_variable: A variable that contains any valid string data type of a string formatted in the Msg_str way. The data type of the @local_variable must be char or varchar, or it must be able to be implicitly converted to these data types.

Severity: User-defined severity level associated with the message. When you use msg_id to raise a user-defined message created with sp_addmessage, the severity specified on Paiserror overrides the severity specified in sp_addmessage.

State: The status number, any integer between 1 at least 127. If you raise the same user-defined error in more than one location, using a unique state for each location is good for finding the code snippet that caused the error.

Argument: A parameter used in place of a variable defined in MSG_STR or corresponding to a msg_id message. There can be 0 or more alternative parameters, but the total number of substitution parameters cannot exceed 20.

Option: Wrong Custom option. LOG: An error is logged in the error log and application log of the SQL Server database engine instance; NOWAIT: Send the message to the client immediately; SetError: Set the @ @ERROR value and error_number value to msg_id or 50000, Do not consider the severity level.

T-SQL run-time generated statements