Execute the SQL statement with embedded parameters -- sp_executesql

Generally, execute SQL statements. Most of them use exec, which has powerful exec functions, but does not support embedded parameters. sp_executesql solves this problem. Copy A Piece Of sqlserver help:

Sp_executesql

Execute statements or batch processing that can be reused multiple times or dynamically generated. Transact-SQL statements or batch processing can contain embedded parameters.

Syntax

Sp_executesql[@ Stmt =]Stmt
[
{,[@ Params =]N '@Parameter_name data_type[,...N]'}
{,[@Param1=]'Value1'[,...N]}
]

Parameters

[@ Stmt =]Stmt

Unicode string that contains a Transact-SQL statement or batch processing,StmtMust be implicitly convertedNtextUnicode constants or variables. More complex Unicode expressions are not allowed (for example, concatenating two strings using the + operator ). Character constants are not allowed. If a constant is specified, N must be used as the prefix. For example, the Unicode constant n'sp _ Who 'is valid, but the character constant 'SP _ Who' is invalid. The size of the string is limited only by the memory of the available database server.

StmtIt can contain parameters in the same format as the variable name, for example:

N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'

StmtEach parameter in@ ParamsThe Parameter definition list and Parameter Value List must have corresponding items.

[@ Params =]N '@Parameter_name data_type[,...N]'

String, which containsStmtThe definition of all parameters in. This string must be implicitly convertedNtextUnicode constants or variables. Each parameter definition consists of the parameter name and data type.NIs a placeholder for the additional parameter definition.StmtEach parameter specified in@ Params. IfStmtIf the transact-SQL statement or batch processing does not contain parameters, you do not need@ Params. The default value of this parameter is null.

[@Param1=]'Value1'

The value of the first parameter defined in the parameter string. The value can be a constant or variable. Must beStmtEach parameter in provides the parameter value. IfStmtIf there are no parameters in the transact-SQL statement or batch processing, no value is required.

N

The placeholder of the value of the additional parameter. These values can only be constants or variables, not more complex expressions, such as functions or expressions generated using operators.

Return code value

0 (successful) or 1 (failed)

Result set

Returns the result set from all SQL statements that generate SQL strings.

Example (thanks to the southern Fujian trading network)

Declare @ user varchar (1000) Declare @ motable varchar (20) Select @ motable = 'mt _ 10' Declare @ SQL nvarchar (4000) -- defines variables. Pay attention to the type. Set @ SQL = 'select @ user = count (distinct userid) from' + @ motable -- assign values to variables -- Execute the statements in @ SQL Exec sp_executesql @ SQL , N' @ user varchar (1000) out' -- indicates that the @ SQL statement contains an output parameter. , @ User out -- similar to calling a stored procedure, specifying the output parameter value Print @ user

In this example, @ motable is an embedded parameter.