Parameterizing Schema name

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Prasvim
    New Member
    • Jul 2008
    • 1

    #1

    Parameterizing Schema name

    Hi
    I am trying to paramertize schema name.Below procedure is working fine if I have ABCD--another parametre as varchar.But when I add a parameter as timestamp it throws an error.
    CREATE PROCEDURE x.SAMPLE_DYN1
    (
    IN SCHEMA_NAME VARCHAR(50),
    IN ABCD VARCHAR(30)
    )
    SPECIFIC x.SQL0807231538 03300
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    INHERIT SPECIAL REGISTERS
    BEGIN
    DECLARE ENO INTEGER DEFAULT 0;
    DECLARE ENAME CHAR(50);
    DECLARE STMT VARCHAR(500);
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE DYN_STMT VARCHAR(500);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND-------------Exit handler from cursor loop
    BEGIN
    set at_end =1;
    RESIGNAL;
    END;

    --DECLARE STMT1 STATEMENT;

    SET STMT =' SELECT NAME FROM '||SCHEMA_NAME| |'.ZZ_EMPT2 WHERE NO='||ABCD||'' ;


    -- EXEC SQL PREPARE S1 FROM :STMT;

    PREPARE STMT1 FROM STMT;
    BEGIN
    --EXECUTE STMT1;
    DECLARE C1 CURSOR FOR STMT1;
    ------ Cursor declaration

    OPEN C1;
    WHILE (at_end = 0) DO
    FETCH C1 INTO ENAME;
    SET ENO = ENO +1;
    SET DYN_STMT = 'INSERT INTO idm_dev.ZZ_EMPT 3(NO,NAME) VALUES(?,?)';
    PREPARE S1 FROM DYN_STMT;
    EXECUTE S1 USING ENO,ENAME;

    END WHILE;
    END;
    COMMIT;
    END;

    Error:
    [IBM][CLI Driver][DB2/LINUXX8664] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=26. SQLSTATE=42884
    Please help
Working...