Terminating A SP Programmatically

deleted_user — Mon, 07 May 2007 15:50:29 GMT

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 '' /
'No Data Found !' / <br> '

' / ;
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 '' /
'Data Found !

' / ;
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 '' /
'Data Found !

' / ;
Put '

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

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

' / ;
Put '

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

topic Terminating A SP Programmatically in Developers

Terminating A SP Programmatically

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

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

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

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

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

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