Passing SELECT statement as parameter in procedure.

Database

Passing SELECT statement as parameter in procedure.

Hi,

 

I'm getting the below error while I'm trying to pass the result of a 'SELECT STATEMENT' as a parameter in the procedure.

 

CALL PROC_NAME ('STRING_1','SELECT MAX(COL1) FROM TABLE1');

Failed. 5497:  CALL cannot be submitted in multi-statement request. 

 

Please let me know if I'm missing something or passing 'SELECT STATEMENT' as parameter in a procedure is not allowed.

 

 

3 REPLIES
N/A

Re: Passing SELECT statement as parameter in procedure.

The error message doesn't seem to be related with your CALL.

 

You currently simply pass a string, but not the result of a Select, try this instead:

CALL PROC_NAME ('STRING_1',(SELECT MAX(COL1) FROM TABLE1));

 

Re: Passing SELECT statement as parameter in procedure.

Hi Dieter, Thanks for the reply. That was a typo from my end. I was trying the below statement only and got that error. CALL PROC_NAME ('STRING_1',(SELECT MAX(COL1) FROM TABLE1)); Regards SHASHIDHAR

Re: Passing SELECT statement as parameter in procedure.

Hi Dieter,

I found the issue. In my procedure, I'm actually passing the parameter from 'SEL COL  FROM VIEW (not table as I specified earlier)'. As this view contains Lockng modifier, it was considered as MSR and hence failed. I removed the locking modifier and it worked .