SQL Server, db2, and oracle Stored Procedure dynamic SQL statement example

SQL Server, db2, and oracle Stored Procedure dynamic SQL statement example

Oracle

CREATE OR REPLACE PROCEDURE a_test
AS
     t_sql VARCHAR2 (2000);
     t_a VARCHAR2 (20);
     t_b VARCHAR2 (20);
     t_c VARCHAR2 (20);
     t_d VARCHAR2 (20);
BEGIN
     t_c: = 'f';
     t_d: = 'g';
    
    
     -This can be any SQL statement such as insert.
     t_sql: = 'SELECT MAX (a), MAX (b) FROM t1 WHERE c =: tempC OR c =: tempD';
    
     EXECUTE IMMEDIATE t_sql
     INTO t_a, t_b --If query assignment is not required, do not write INTO XXX statements here.
     USING t_c, t_d-If you don't need to use variables, don't write USING XXX statements.
     ;
END a_test;
/

-Dynamic execution statement with return cursor.
CREATE OR REPLACE PROCEDURE a_test2
(
     o_cursor OUT SYS_REFCURSOR
)
AS
     t_sql VARCHAR2 (2000);
     t_a VARCHAR2 (20);
     t_b VARCHAR2 (20);
     t_c VARCHAR2 (20);
     t_d VARCHAR2 (20);
BEGIN
     t_c: = 'f';
     t_d: = 'g';
    
    
     -This can be any SQL statement such as insert.
     t_sql: = 'SELECT * FROM t1 WHERE c =: tempC OR c =: tempD';

     OPEN o_cursor FOR t_sql
     USING t_c, t_d-Also if you don't need to use variables, don't write USING XXX statements.
     ;
END a_test2;
/

SQL Server

CREATE PROCEDURE a_test
AS
    DECLARE @t_sql NVARCHAR (2000); --sql server dynamic statement to declare as NVARCHAR type.
    DECLARE @t_a VARCHAR (20);
    DECLARE @t_b VARCHAR (20);
    DECLARE @t_c VARCHAR (20);
    DECLARE @t_d VARCHAR (20);
BEGIN
    SET @t_c = 'f';
    SET @t_d = 'g';
    
    -This can be any SQL statement such as insert.
    SET @t_sql = 'SELECT @tempA = MAX (a), @tempB = MAX (b) FROM t1 WHERE c = @tempC OR c = @tempD';
    
    --Do not concatenate strings after EXECUTE SP_EXECUTESQL
    EXECUTE SP_EXECUTESQL @t_sql
    
    --Without parameters, the following declaration variables and incoming parameters do not need to be written.
    --Declaring variable types and input and output parameters. Must be written on one line, not line breaks.
    , N '@ tempA VARCHAR (20) OUT, @tempB VARCHAR (20) OUT, @tempC VARCHAR (20), @tempD VARCHAR (20)'
    -Parameter value. The order of the incoming variables must be the same as the order of the declared variables.
    , @ t_a OUT, @t_b OUT, @t_c, @t_d
    ;
END
GO
--sql server returns the same cursor as ordinary statements, and you can directly assign t_sql to select * from XXX.

Db2

CREATE PROCEDURE a_test
(
    v_c VARCHAR (20)
)
BEGIN
    DECLARE t_sql VARCHAR2 (2000);
    DECLARE t_a VARCHAR2 (20);
    DECLARE t_b VARCHAR2 (20);
    DECLARE t_c VARCHAR2 (20);
    DECLARE t_d VARCHAR2 (20);
    -The t_stmt after FOR must be the same as the variable after prepare below. Its type is statement.
    DECLARE t_cur CURSOR FOR t_stmt;

    SET t_c = 'f';
    SET t_d = 'g';
    
    -This can be any SQL statement such as insert.
    SET t_sql = 'SELECT MAX (a), MAX (b) FROM t1 WHERE c =: tempC OR c =: tempD';
    
    --I haven't found other ways to replace it at this moment, only use cursor to read:
    PREPARE t_stmt FROM t_sql;
    
    OPEN t_cur
    -If you do not need to use variables, do not write USING XXX statements.
    USING t_c, t_d
    ;
    
    FETCH t_cur INTO t_a, t_b;
    CLOSE t_cur;
END

/ *
Dynamic statements with cursor return are the same as ordinary statements, but when declaring a cursor, you must declare a cursor of the return type
And before the begin keyword:
DYNAMIC RESULT SETS 1
LANGUAGE SQL
* /

CREATE PROCEDURE a_test2
(
    v_c VARCHAR (20)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE t_sql VARCHAR2 (2000);
    DECLARE t_a VARCHAR2 (20);
    DECLARE t_b VARCHAR2 (20);
    DECLARE t_c VARCHAR2 (20);
    DECLARE t_d VARCHAR2 (20);
    -The t_stmt after FOR must be the same as the variable after prepare below. Its type is statement.
    DECLARE t_cur CURSOR WITH RETURN FOR t_stmt;

    SET t_c = 'f';
    SET t_d = 'g';
    
    -This can be any SQL statement such as insert.
    SET t_sql = 'SELECT MAX (a), MAX (b) FROM t1 WHERE c =: tempC OR c =: tempD';
    
    PREPARE t_stmt FROM t_sql;
    
    OPEN t_cur
    -If you do not need to use variables, do not write USING XXX statements.
    USING t_c, t_d
    ;
END