If else and select together in macro/SP

Database
Enthusiast

If else and select together in macro/SP

Hi ,

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.

thanks !

3 REPLIES
Junior Contributor

Re: If else and select together in macro/SP

#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;
select ....;
);

#2: of course you can return a result set from an SP, but you have to use wrap it:

replace procedure sp (...)
dynamic result sets 1
begin
declare c cursor with return only for
select ....;
open c;
end;

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.

Dieter


Enthusiast

Re: If else and select together in macro/SP

Hi Diether,

I tried the macro, bui t doesn't display any error message. I just tried :

replace macro mymacro (inDate date) as

( abort 'inDate should be within the last 10 days'

  where :inDate < date -10;

  

);


and called exec mymacro ('2013-01-01') or exec mymacro(date-30). It says execute complete, 0 rows processed. No error message is displayed.

Thanks !

Junior Contributor

Re: If else and select together in macro/SP

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.

Dieter