How to capture errors in Stored Procedures

Question

Post reply

How to capture errors in Stored Procedures

James Chan

SSC Veteran

Points: 283
More actions
September 14, 2005 at 10:46 pm

#165950
I have a Delphi application that uses ADO to call SQLServer 2000 stored procedures. When errors occurred in the SProc, the ADO does not always get the error and return to Delphi. As a result, the application continues as if the SProc has completed successfully.

The problem occurs when there are multiple "select" queries in the SProc. If the first query succeeds, the ADO will ignore the failures of the subsequent queries. For example is:
```
create procedure spTestError
as
begin
  select * from sysfiles
  
  -- this will fail when called 2nd time
  select * into DummyTable from sysfiles
end
```
If I call the spTestErros 2 or more times in QA, error occurs.

However, when calling multiple times from Delphi via ADO, Delphi never knows the above SProce fails. This happens when I use TADOStoredProc or TADOCommand. For the TADOConnection, I set CursorLocation to clUseClient.

Any suggestions?

James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dec_obrien Default port Points: 1482 More actions · Answer 1

dec_obrien

Default port

Points: 1482

September 15, 2005 at 9:00 am

#590178

Somebody posted this link as an answer to my question on error handling in Stored Procedures a while ago:

http://www.sommarskog.se/error-handling-II.html

DCPeterson SSCoach Points: 15256 More actions · Answer 2

This is really a programming question. SQL Server is returning the error, but somewhere along the way it is being ignored by the application.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Tin Le SSC Enthusiast Points: 100 More actions · Answer 3

/*create procedure spTestError

as

begin

select * from sysfiles

-- this will fail when called 2nd time

select * into DummyTable from sysfiles

end

/*

You do not have a return value here.

Must be this

create procedure spTestError

as

begin

select * from sysfiles

-- this will fail when called 2nd time

select * into DummyTable from sysfiles

Return (@@Error)

end

in the Delphi you must call.

With spProcedure do begin

DataType := ftInteger;

Name := 'RETURN_VALUE';

Direction := pdReturnValue;

end;

intReturn := spProcedure.Parameters.ParambyName('RETURN_VALUE').Value;

Check the intReturn here.

Tin Le

DCPeterson SSCoach Points: 15256 More actions · Answer 4

You don't have to use the return code although you can. SQL Server will return any error or information messages to the client without explicitly defining the return code like your example.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/