Check For Error In SQL PROC

[mmignot]

Hello all,

Does anyone know how I can check for an error that originates in an SQL Procedure? I'd like to check for a bad return or error code. Here is a sample of the query:

create table myown_data as
select * from connection to db2
( select distinct
a.CUST_ACCT
,a.PCT_RT
,a.CONT_DT
,a.MAT_DT
,a.FIN_MAT_DT
,a.LOAN_TYPE
,a.ORG_RT

from heids.he_account a
,heids.he_account_msr b

where a.cust_acct_id = b.cust_acct_id
and b.mnth_dte = &prior_mnth1
and a.PORT_CD = 'ABC'

order by a.cust_acct
for fetch only
);

Any help/suggestions would be appreciated.

Thanks,
Mark

 

[ChrisW75]

There are 2 macro variables created by Proc SQL to tell you the status... I'll look them up, if I don't get back to you (I'm eating lunch atm) that'll give you a starting point.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[ChrisW75]

This is what you need, go to the bottom of this page:-

http://support.sas.com/onlinedoc/913/getDoc/en/mcrolref.hlp/a001066135.htm

&SQLRC is what you need.

This has more info:-

http://support.sas.com/onlinedoc/913/getDoc/en/proc.hlp/a000146905.htm#a002473704

Enjoy.
Chris.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[ChrisW75]

You actually want SQLXRC and SQLXMSG as you are using the passthrough engine (ie connecting direct to the dbms)

You aren't the Mark I know are you?

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[mmignot]

Chris, thanks for the info. Not sure of we know each other, I'm based in Northern California. Know anyone from Sacramento?

Mark