Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: passing sql query as a parameter to db2 stored procedure

    Hi,
    Someone plz tell me how to pass an sql query to a stored proc. My stored proc is like:
    Code:
    MYPROC(IN IN_SQL VARCHAR(10000))
    I am using EXECUTE IMMEDIATE to execute this query that has been passed as argument. As the input parameter would take an sql query it would contain single quotes around date fields and other char fields where assignments are done (eg.insert into myTable(col1,col2) values('abcdef', '01-01-11') ). So I cant call it like call MYPROC(" insert into myTable(col1,col2) values('abcdef', '01-01-11') ") , can I? Usually we pass strings to a proc in single quotes.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try:
    call MYPROC('insert into myTable(col1,col2) values(''abcdef'', ''01-01-11'') ')

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You will have to escape the nested single-quotes by doubling them. Another option is to pass the string via parameter markers.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Mar 2011
    Posts
    4
    @tonkuma Thank u very much for your reply. I tried
    Code:
    call MYPROC('insert into myTable(col1,col2) values(''abcdef'', ''01-01-11'') ')
    but it doesnt seem to work.

    @stolze
    Thank u very much. Escaping nested single quotes with single quotes worked brilliantly.
    So this works:
    Code:
    call MYPROC(' insert into myTable(col1,col2) values(''abcdef'', ''01-01-11'') ')
    Now Suppose the proc's definition is changed to
    Code:
    MYPROC(IN IN_SQL CLOB(1M))
    In this case how would we call the proc , again if we have to pass a query? Would escaping the nested single quotes work again? Thanks in advance.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by DushyantG View Post
    Now Suppose the proc's definition is changed to
    Code:
    MYPROC(IN IN_SQL CLOB(1M))
    In this case how would we call the proc , again if we have to pass a query? Would escaping the nested single quotes work again? Thanks in advance.
    Same as above. No difference
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    @tonkuma Thank u very much for your reply. I tried
    Code:
    call MYPROC('insert into myTable(col1,col2) values(''abcdef'', ''01-01-11'') ')
    but it doesnt seem to work.

    @stolze
    Thank u very much. Escaping nested single quotes with single quotes worked brilliantly.
    So this works:

    Code:
    call MYPROC(' insert into myTable(col1,col2) values(''abcdef'', ''01-01-11'') ')
    DushyantG,
    What error did you received from my code?

    I saw only difference between my code and Stolze's was a blank before "insert ...".

Tags for this Thread

Posting Permissions

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