Both sp_executesql and executers can dynamically perform strings, but sp_executesql is more common than Execute, which is more likely to be reused than SQL Server (SQL Server online document) sp_executesql [@stmt =] STMT [{, [@Params =] n '@ parameter_name data_type [, ... n]'} {, [@ param1 =] value1 '[, ... n]}]
Parameters [@stmt =] STMT
The Unicode string containing Transact-SQL statements or batch, the STMT must be implicitly converted to NTEXT Unicode constants or variables. More complex use is not allowed
Unicode expressions (eg, using a operator series two strings). The character constant is not allowed. If constant is specified, N must be used as a prefix. For example, Unicode often
The amount n'sp_who 'is effective, but the character constant' sp_who 'is invalid. The size of the string is limited only by the available database server memory limit.
The STMT can contain the same parameters as the variable name, for example:
N'Select * from Employees where Employeeid = @IDParameter '
Each parameter contained in the STMT must have a corresponding item in the @Params parameter definition list and the parameter value list.
[@PARAMS =] n '@ parameter_name data_type [, ... n]'
String, which contains definitions of all parameters embedded in the STMT. This string must be an unicode constant or variable that can be implicitly converted to NTEXT. Each parameter definition is
The parameter name and data type are composed. n is a placeholder that indicates additional parameter definitions. Each parameter specified in the STMT must be defined in @params. If Transact-SQL in Stmt
The statement or batch does not contain parameters, no @params. The default value of this parameter is NULL.
[@ param1 =] 'Value1'
The value of the first parameter defined in the parameter string. This value can be a constant or variable. The parameter value must be provided for each parameter included in the STMT. If the STMT is included
TRANSACT-SQL statements or batch does not require values.
n The placeholder of the value of the additional parameter. These values can only be constant or variables, and cannot be more complex expressions, such as functions or expressions generated using operators.
2, @ STMT can only be the 'ntext / nchar / nvarchar' type
3, the execution result storage sp_executesql two ways (1) declare @TmpSelect NVarchar (600) declare @num int select @TmpSelect = 'set nocount on; select @SPintRootRecordCount = count (*) from tb_resinfo' exec sp_executesql @TmpSelect, N '@Spintrootrecordcount Int output', @num output select @num
