Terminating A SP Programmatically

[deleted_user]

All:

I'm sure others in this forum have already successfully dealt with this issue. What is the best/proper way to conditionally terminate a SP and deliver a message to the user ?

The sample code below does terminate the SP based on a query not returning any rows, but we still receive a stored process message about abnormal termination after the message from the program itself.

To get around this, we "eliminated" the SP error message by "cheating" we changed background color to black.

There has to be a better, more graceful way to do condition/exception handling in stored processes.

Thank You.

-Gerry

Gerard T. Pauline
Mgr, Internet/DB Applications
Computer Systems, DoIT
Pace University

========================================================

%Macro ROWS (DS) ;

%Global DSID NLOBSF RC ID ;

%Let DSID = %Sysfunc(Open(&DS));

%If (&DSID) %Then
%Do ;
%Let ROWS = %Sysfunc(Attrn(&DSID, NLOBSF)) ;
%Let RC = %Sysfunc(Close(&DSID)) ;
%End ;

%If (&ROWS = 0) %Then
%Do ;
Data _NULL_ ;
File _WEBOUT ;
Put '' /
'

' / ;
Put '

' /
'' /
'' /
'' /
'

' /
'

There was no data found that satisfied the search ' /
'conditions specified !

' / ;
Put '

' / ;

Call Execute ('EndSAS ;') ;
Call Execute ('%Str(;) ;') ;
Run ;
%End ;
%Else
%Do ;
Data _NULL_ ;
File _WEBOUT ;
Put '' /
'

' / ;
Put '

' /
'' /
'' /
'' /
'

Data was found for this query -- ' /
'YIPPEEEE !

' / ;
Put '

' / ;
Call Execute ('%Str(;) ;') ;
Run ;
%End ;

/*

%If (&ROWS = 0) %Then
%Do ;
Data _NULL_ ;
PutLog 'There are no rows in the table !' ;
Call Execute ('EndSAS ;') ;
Call Execute ('%Str(;) ;') ;
Run ;
%End ;
%Else
%Do ;
Data _NULL_ ;
PutLog "There are &ROWS rows in the table" ;
Call Execute ('%Str(;) ;') ;
Run ;
%End ;
*/

%Mend ROWS ;

Proc SQL ;
Create Table IsErika As
Select *
From SATURN.SPRIDEN
Where SPRIDEN_PIDM = &ID ;
Quit ;

%Rows (IsErika)

Data _NULL_ ;
File _WEBOUT ;
Put '' /
'

' / ;
Put '

' /
'' /
'' /
'' /
'

Processing continues-------------' /
'YIPPEEEE !

' / ;
Put '

' / ;
Call Execute ('%Str(;) ;') ;
Run ;