Stored Procedure execution status

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure execution status

Author

Topic

KenA
Starting Member

28 Posts

Posted - 2005-12-02 : 12:47:56

Hi. When SqlServer executes a procedure (any type: select, update, insert) after it´s executed can I get a default status for this executed procedure, like a return bool value from SqlServer as true for successfull and false for failed to execute?

»»» Ken.A

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 12:53:49

UPDATE MyTable SET MyColumn = 'FOO' WHERE MyColumn = 'BAR'
SELECT @@ERROR

Note that @@ERROR only works for the immediately preceding action, so most commonly it is stored in another variable and then tested:


DECLARE @intErrNo INT
...
UPDATE MyTable SET MyColumn = 'FOO' WHERE MyColumn = 'BAR'
SELECT @intErrNo = @@ERROR
IF @intErrNo <> 0
BEGIN
    SELECT 'Something dredful happened', @intErrNo
    ROLLBACK
    GOTO EXIT_MY_SPROC
    ....
END

Kristen

KenA
Starting Member

28 Posts

Posted - 2005-12-02 : 13:10:00

It appears to be a good: 'best practices' to be implemented, but I´m not sure whether it´s a good idea to implement in all procedures in the DB, since I never saw anyone doing it here where I work. (guess why I came to that question)

»»» Ken.A

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 13:54:43

You could do worse than sticking

SET XACT_ABORT ON

as the first line of your SProc "body" which will cause the SProc to Abort in most error conditions (NOT including EXEC'ing an Sproc which it, itself, fails)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-03 : 13:16:37

quote:
Originally posted by KenA

It appears to be a good: 'best practices' to be implemented, but I´m not sure whether it´s a good idea to implement in all procedures in the DB, since I never saw anyone doing it here where I work. (guess why I came to that question)

»»» Ken.A

A lot of places don't use error handling in the stored procedures. They depend on the application to handle it. That doesn't make it a "good" practice though. Anytime you have the ability to put error handling in the stored procedures and further enforce data rules, you should do so.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-12-05 : 02:45:11

"They depend on the application to handle it"

Much more difficult to do

BEGIN TRANSACTION
... Do This ...
If SomeError GOTO HandleError
... Do That ...
If SomeError GOTO HandleError
COMMIT
RETURN 0

HandleError:
ROLLBACK
RETURN 1

type coding from the application once you start using SProcs, and they get reasonably "evolved".

Kristen

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-05 : 03:14:43

All stored procs will yield a return value of type int. 0 (zero) indicates nothing went wrong. Minus values indicate errors.

Any other command will spew back a full blown SQL Exception.

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources