|
Microsoft SQL Server
|
EXEC [UTE] sp_executesql
N'dynamic_compound_string' | @dynamic_statement [
N'param_defined_string' | dynamic_param_definition [@param1=]value1 [,[@paramN=]valueN]...
]
|
The EXECUTE statement with sp_executesql executes a SQL statement including dynamic compound statements. Dynamic compound statements can contain embedded parameters.
Parameters:
N'dynamic_compound_string' | @dynamic_statement - executable SQL statement that can be defined as a string or variable.
N'param_defined_string' | dynamic_param_definition - string or variable which specifies input parameters for the executable SQL statement.
Each parameter definition consists of a parameter name and its data type. The default value for the parameter is NULL
[@param1=] value1 is a value of the parameter. The value can be a constant or a variable. There must be a value specified for every parameter included in the dynamic statement.
Example:
The following statement executes a parameterized dynamic select statement with the input parameter:
execute sp_executesql N'select * from tab1 where col1 = @param',
N'@param int',
@param = 35
|
|
Oracle
|
EXECUTE IMMEDIATE dynamic_string
[ INTO { ret_value1 [, retvalueN]... | record_name} ]
[ USING [ {IN | OUT | IN OUT} ] value1, [,valueN]...];
|
The EXECUTE IMMEDIATE statement executes dynamic SQL statements in Oracle
Parameters:
dynamic_string - executable SQL statement that can be defined as a string, variable or as expression. Input parameters in are marked as :N, where N is the number of the parameter in the USING clause.
ret_valueN, record_name - the INTO clause specifies the variables or a record into which the column values are retrieved. The INTO clause can be used only for single-row queries.
[ IN | OUT | IN OUT ] valueN - the USING clause specifies a list of input/output values of the parameters. By default, valueN is IN parameter.
Examples:
The sample below executes a dynamic compound insert statement with input parameters:
sql_dString := 'INSERT INTO tab1 VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_dString USING 77, 21, variable1;
|
