passing sql query as a parameter to db2 stored procedure

DushyantG · #1 (**permalink**) 03-30-11, 10:21

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.

tonkuma · #2 (**permalink**) 03-30-11, 10:41

Try:
call MYPROC('insert into myTable(col1,col2) values(''abcdef'', ''01-01-11'') ')

stolze · #3 (**permalink**) 03-30-11, 10:42

You will have to escape the nested single-quotes by doubling them. Another option is to pass the string via parameter markers.

DushyantG · #4 (**permalink**) 03-31-11, 01:27

@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.

sathyaram_s · #5 (**permalink**) 03-31-11, 04:00

Quote:

Originally Posted by DushyantG

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

tonkuma · #6 (**permalink**) 03-31-11, 04:16

Quote:

@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 ...".

Internet Services

Web Development

Digital Marketing

Consumer Tech