I want to execute the below call procedure statement using a dynamic sql within a For loop of stored procedure.
call dbc.sysexecsql ('CALL dbname.'||proc_name_variable||'('||v1||','||v2||','||v3||','||v4||','||''||timestamp_variable||''||');' ) ;
But When i try to compile the procedure i am getting the following error.
'Missing/Invalid SQL statement'E(5407):Invalid operation for DateTime or Interval.'
please suggest any possible solutions on this. I need to enclose the timestamp variable within quotes while calling that internal procedure.
you have to use an implicit typecast for a TIMSTAMP column:
CAST(timestamp_variable as VARCHAR(24).
But i doubt you can use dynamic SQL to CALL an SP.
Casting that Timestamp variable into a varchar is working fine. I also had a doubt whether we are able to call a procedure dynamically within another procedure. But when i ran after casting, the intetrnal procedure is not executed at all.
Just for checking, I placed the direct call statement instead of this dynamic sql with the actual varaible values(sample). It worked fine.
Call dbname.proc_name('a','b','001',2,3,null,cast('2013-07-11 12:32:10.00000' as varchar(24)));
But i cannot have like this since there are around 100 rows in that loop with different variable combo. so i need that call statement to be run for those many times. Is there any other option to call the procedure with all the variable values fetching from output of a FOR loop.
Thanks in Advance,
unless it's a different SP for every CALL you can simply pass the variables as parameters instead of Dynamic SQL:
Call dbname.proc_name(v1,v2,v3,V4,V5,null,cast(v7 as varchar(24)));
That Proc name is also a variable, the value of which is again fetched from that FOR loop only. There are around 10 procedures to be called around 100 times with different set of variables. Please suggest.
If anyone is still interested the below code will work
WHERE f.txn_cre_ts BETWEEN (DATE -3 || ' ' || '00:00:00') AND (DATE -1 || ' ' || '23:59:59')