Example:
EXEC SP_EXECUTESQL
N'select role.userid from role where role.userid = @ParamuserId '
, N '@ paramuserid nvarchar (4000)'
@Paramuserid = N'123457 '
Description:
SP_EXECUTESQL
Perform a Transact-SQL statement or batch processing that can be reused or dynamically generated multiple times. Transact-SQL statements or batch can include embedded parameters.
grammar
sp_executesql [@stmt =] STMT
[
{, [@Params =] n '@ parameter_name data_type [, ... n] [out]'}
{, [@ param1 =] 'Value1' [, ... n]}
]
parameter
The STMT must be an unicode constant or variable that can be implicitly converted to NTEXT. More complex Unicode expressions are not allowed (eg, two strings are used to use operators). The character constant is not allowed. If constant is specified, N must be used as a prefix. For example, Unicode constant 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.
When there is a parameter in the STMT, there must be a corresponding parameter type declaration and designated parameter value: common parameter type has nVARCHAR (4000), int (4000 is NVARCHAR's maximum)
N '@ parameter_name data_type [, ... n] [out]'
If the parameter is used to store the return value, add a keyword 'out' after the parameter type, such as:
Declare @user varchar (1000)
Declare @Motable Varchar (20)
SELECT @Motable = 'MT_10'Declare @SQL NVARCHAR (4000)
Set @ SQL = 'SELECT @User = Count (DistINCT Userid) from' @ Motable
EXEC SP_EXECUTESQL @SQL
, N '@ user varchar (1000) OUT' - Indicates that the statement in @SQL contains an output parameter
, @ user out - and call stored procedures, specify output parameter values
Print @user
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 consists of parameter names and data types. n is a placeholder that indicates additional parameter definitions. Each parameter specified in the STMT must be defined in @params. If the Transact-SQL statement or batch in the STMT does not contain parameters, it is not required to @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 TRANSACT-SQL statement or batch included in the STMT does not require a value.
n
Attach the value of the value of the parameter. These values can only be constant or variables, and cannot be more complex expressions, such as functions or expressions generated using operators. Returns the code value
0 (success) or 1 (failed)
Result set
Returns the result set from all SQL statements that generate SQL strings.
Use in the code:
Private string strsql = "EXEC SP_EXECUTESQL
N'select role.userid from role where role.userid = @ParamuserId '
, N '@ paramuserid nvarchar (4000)'
@Paramuserid = N'123457 '";
Private sqlconnection m_objcon = NULL;
Private sqlcommand m_objcmd = NULL;
m_objcon = new SqlConnection ("DataSource = 10.69.0.13; userid = sa; password = sa");
m_objcon.open ();
m_objcmd = m_objcon.createCommand ();
m_objcmd.commandtext = strsql;
m_objcmd.parameters.clear ();
IF (txtuserid.text == null)
{
m_objcmd.parameters.add ("@ parauserid", system.dbnull.value;
}
Else
{
m_objcmd.parameters.add (("@ parauserid", txtuserid.text);
}
SqlDataAdapter Tempdad = New SqlDataAdapter (m_objcmd);
DataSet objds = new dataset ();
Tempdad.Fill (Objds);
Int irowcount = Objds.tables [0]. ROWS.COUNT;
