Error handling for select query

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

SQL Server 2000 Forums

SQL Server Development (2000)

Error handling for select query

Author

Topic

joseln
Starting Member

11 Posts

Posted - 2008-04-29 : 11:12:36

Hi
I have written a Insert to table from a select query. so to handle error i check the @@error after the select query. but even if error occur the @@error didnt return any value? so i am unable to handle error. how to slove this .i am using sql2000.

the sp is as below
-----------------------------------------------------
create proc test_err
as

DECLARE @A INT, @B INT
SET @A=0
SET @B=0

Insert into #Tmp...
select a, sum(c) from ......
group by a,b

SELECT @A = @@ERROR IF @A <> 0 GOTO ERR_HANDL

IF @A = 0
RETURN 0

ERR_HANDL:
PRINT 'RETURN VAL' + ' ' + CAST(@A AS VARCHAR)
RETURN @A
----------------------------------------------------------

but as a result of running this sp, neither 0 nor any other value return. actually this sp has to return error as select qry's group by having error.

but i need @A to return value that i handle in some calling SP as
declare @X int
EXEC @X = test_err

how to get @A return value of select query has error?.

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-30 : 06:54:59

PLEASE SEND THE COMPLETE QUERY WHICH IS GENERATING ERROR.

chandan Joshi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-30 : 08:55:00

<<
Insert into #Tmp...
select a, sum(c) from ......
group by a,b
>>

You should have created the temp table and then insert

Madhivanan

Failing to plan is Planning to fail

joseln
Starting Member

11 Posts

Posted - 2008-05-01 : 12:53:46

Madhivanan

consider the qry return error by whatever means, but my question is why the @@error i have handled below the qry didnt return value > 0 ?.

why the @@error below the select qry not return > 0?.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-01 : 13:39:30

error handling in sql server (especially prior to 2005) is not a robust as evironments you may be used to. Depending on the type of error and severity sql will execution will halt at the error and never flow into your error handler routine. For those types of errors you will need to anticipate the possibility of the error and test for the condition before you attempt the action.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources