I have a basic question , in which construct can we have select and an If-Else logic together. I know we can he If-else in a procedure, but a select is not allowed. And we can have a select in a macro, but if-else is not allowed.
So, is there any construct (functions or anything ), in which i can have both. I have to create a report regularly which i want to call in a proc / macro/??, but i also want to put a check on a date column that a user might pass which should not to be greater that 60 days.
#1: you don't need if/else in that case, simply return an error message for invalid dates:
replace macro mymacro (inDate date) as
( abort 'inDate should be within the last 60 days'
where :inDate < date -60;
replace procedure sp (...)
dynamic result sets 1
declare c cursor with return only for
Btw, even if the syntax includes "cursor" it's not processed like a cursor (row by row), it's just an ordinary answer set.
When you can do a query like this in both ways, SP and macro, the macro should be preferred s there's less overhead.
I tried the macro, bui t doesn't display any error message. I just tried :
and called exec mymacro ('2013-01-01') or exec mymacro(date-30). It says execute complete, 0 rows processed. No error message is displayed.
You probably use SQL Assistant and there was a problem related to that (i can't remember which release).
Try to upgrade to a newer release/patch level or try connecting with .NET instead of ODBC.