-
1. Re: Is it possible to use an interval type as an input parameter in a virtual procedure?
rareddy Feb 1, 2017 10:08 AM (in response to gadeyne.bram)1 of 1 people found this helpfulSo your question is whether you can use "SQL_TSI_HOUR" Virtual procedure as input parameter?
When used used outside of the TIMESTAMPADD it has no meaning, but you can pass it in string/int form and convert to right constant inside your procedure.
-
2. Re: Is it possible to use an interval type as an input parameter in a virtual procedure?
gadeyne.bram Feb 1, 2017 10:18 AM (in response to rareddy)Hi Ramesh,
Thank you for your answer!
So just to be sure I assume that you mean something like this:
exec v_proc_a('SQL_TSI_HOUR', ...)
and then within the procedure do something like:
BEGIN
...
... TIMESTAMPADD(CASE WHEN interval = 'SQL_TSI_HOUR' then SQL_TSI_HOUR WHEN 'SQL_TSI_DAY' then SQL_TSI_DAY ... end, ...)
...
END
-
3. Re: Is it possible to use an interval type as an input parameter in a virtual procedure?
shawkins Feb 1, 2017 10:22 AM (in response to gadeyne.bram)Unfortunately it's a non-reserved keyword, so it doesn't have a simple value representation. You would have to use dynamic sql, or a different case expression:
case when interval = 'SQL_TSI_HOUR' then TIMESTAMPADD(SQL_TSI_HOUR ...) when interval = ... end
-
4. Re: Is it possible to use an interval type as an input parameter in a virtual procedure?
gadeyne.bram Feb 1, 2017 10:26 AM (in response to shawkins)Ok thank you!
I'll give it a try.