Table of ContentsPreviousNext

Ispirer             Ispirer

Ispirer SQLWays Database Migration Software

Executing Dynamic SQL Statements with Parameters

This section describes execution of parameterized dynamic SQL statements in various databases and their conversion by SQLWays.

TABLE 41. Executing Dynamic SQL Statements with Parameters
Microsoft SQL Server
EXEC [UTE] sp_executesql  
N'dynamic_compound_string' | 
@dynamic_statement         [  
 N'param_defined_string' | 
The EXECUTE statement with sp_executesql executes a SQL statement including dynamic compound statements. Dynamic compound statements can contain embedded 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.
The following statement executes a parameterized dynamic select statement with the input parameter:
execute sp_executesql           N'select * from tab1 where col1 = 
N'@param int', 
@param = 35 
   [ INTO { ret_value1 [, 
retvalueN]... | record_name} ] 
   [ USING [ {IN | OUT | IN OUT} ] 
value1, [,valueN]...]; 
The EXECUTE IMMEDIATE statement executes dynamic SQL statements in Oracle
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.
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;

Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.