Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Need help single quote in Db2 stored Proc Parameter dymanically passed

    Need help single quote in Db2 stored Proc Parameter dynamically passed

    I have stored Proc which accepts the string parameter. This string parameter is where clause which has single quote. Can someone please help how can I store this parameter in the string and use in dynamic sql with in the Stored Proc.
    I know that we can use ‘’ (two single quote) as escape character and it should work. In my case parameter is built dynamically and not sure where will I have single quote.
    Also I can stored this parameter in the session table and use this session table later in the stored proc. But none of the approach seems to be working due to unpredictable position of the single quote.

    ----------To store in the session table-----
    Declare Global Temporary Table T1
    (
    Param1 VARCHAR(100),
    Param2 VARCHAR(100)
    )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS
    NOT LOGGED;
    Insert Into Session.T1
    Select '(tbl.col1 in (‘001’,’XYZ’,099)' As Param1,
    '(tbl2.col3 in (‘ABCDE’, ‘NOT’)' As Param1
    From SYSIBM.SYSDUMMY1;

    Param1 value will be (tbl.col1 in (‘001’,’XYZ’,099)
    Param2 value will be (tbl2.col3 in (‘ABCDE’, ‘NOT’)

    If I create the stored Proc

    Declare Global Temporary Table T1
    (
    Param1 VARCHAR(50),
    Param2 VARCHAR(50)
    )
    ON COMMIT PRESERVE ROWS
    NOT LOGGED;

    CREATE PROCEDURE schema.Test()
    NULL CALL
    LANGUAGE SQL
    Begin
    Declare stmt varchar(1000);

    Stmt = ‘select * from tblx tbl where ‘ || param1;
    Execute immediate stmt

    end;

    commit;
    --------------------------------
    Due to single quote i can't store param1 and param2 in the session table "T1" nor I can pass them directly to stored proc parameter like

    CREATE PROCEDURE schema.Test(In Param1 Varchar(100),In Param2 Varchar(100))
    NULL CALL
    LANGUAGE SQL

    thank you for any help or suggestion.
    Last edited by Pras; 04-10-12 at 05:29.

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    You can quote ' with another ' as in:

    values 'That''s all folks'
    --
    Lennart

  3. #3
    Join Date
    Apr 2012
    Posts
    2
    Thanks for your reply. You are correct but the problem is param1 and param2 are dynamically built and i can't modify it before it is passed to Stored Proc. So whatever I need to do; I have to do inside the Stored proc and i don't know how can I do as it won't even late store in the Variable without escape char (').

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •