Store Procedure Return Codes & Failed Query Txt

Database
N/A

Store Procedure Return Codes & Failed Query Txt

Hi All

Just wanted to know if we can capture the actual query that failed while a stored procedure was executing.

I tried capturing Activity Count, SQL State, SQL code - but just in case wanted to know if we can caprture the Query TXT that failed.

All i am trying to do here is, i have a stored procedure which executes 100's of SQL's. Is setting the temp varaible to track it down during the EXIT HANDLER the only way to find out which query failed or is there a better way to track the particular statement which failed.

Any help would be greatly appriciated.

Thanks !

Anand

1 REPLY
TD2
N/A

Re: Store Procedure Return Codes & Failed Query Txt

Hi Anand

One way is to declare a a variable of length around varchar(10000) and assign your query to it. Later create a table which has column names as your procedure name, step name, and the sql string (varchar(600)). If you feel your query is longer than 600 characters then take a substring and insert in steps.

DECLARE QR VARCHAR(10000);

SET QR = 'INSERT INTO.....'

INSERT INTO DB.TBL('MYPROC','2NDSTEP',SUBSTR(QR,1,600));

INSERT INTO DB.TBL('MYPROC','2NDSTEP',SUBSTR(QR,601,1200));