How to use @@Error in store procedure.

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

How to use @@Error in store procedure.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-18 : 01:34:13

Dear All,

I am using following store procedure. In this I am using two cursors. After that I am checking if any error occurs with IF(@@Error=0).
But If any error is getting or not therefore also it's going into,
IF(@@Error=0)
BEGIN
RETURN 0
END

I want if any error occurs then only go to ELSE part other wise return 0.
Can anybody please tell me what's wrong I am doing in SP ?

ALTER PROCEDURE [dbo].[pr_lcr_norm]
@rtshtid varchar(36)
AS
SET IMPLICIT_TRANSACTIONS OFF

/* DECLARATIONS */

DECLARE @worksheetid varchar(36);

DECLARE wrkshtcursor CURSOR LOCAL READ_ONLY FOR
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE nrscursor CURSOR LOCAL READ_ONLY FOR
WHILE @@FETCH_STATUS=0
BEGIN
Print 'Some calculations is running.'
END
CLOSE nrscursor;
DEALLOCATE nrscursor;

CLOSE wrkshtcursor;
DEALLOCATE wrkshtcursor;
END

IF(@@Error=0)
BEGIN
RETURN 0
END
ELSE
BEGIN
print 'Error at ' + convert(varchar,getdate(),9);
END

Thanks and Regard's
Harish Patil

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 01:43:59

DECLARE @ERROR, int, @@ROWCOUNT int

..sql statemet

IF @EEOR =...

Need more???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 01:45:45

OK..why are you sing a cursor..post that code said codd

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 01:51:43

are you using sql 2005 or above? if yes, use TRY...CATCH instead. Otherwise you have to capture @@ERROR after each operation for which you want to check error status in variables and then check if finally under IF as @@ERROr will be reset after each sql statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-18 : 01:51:56

Hello Friend,

After closing cursors, I am using following code for checking If any error occurs. If no error occurs it will return 0 otherwise it should return errror.

My confusion is I think i am putting following code on wrong place because If any error occurs while executing cursor that time is going in If statement if not therefore also it'd going inside IF statement.

I want If any error occurs while executing cursor then It should go in ELSE part.

IF(@@Error=0)
BEGIN
RETURN 0
END
ELSE
BEGIN
print 'Error at ' + convert(varchar,getdate(),9);
END

Thanks and Regard's
Harish Patil

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 01:58:56

OK, back to basics...are you from bangalore?

And WHY do you need a cursor

And IF you need a cursor, every operation should be checked, and rolled back if there's a failure

I only ask, because I have off shore resources..and IF you are using SQL Server, and I am your hiring company, I want to know who is tellin you to do this...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 02:02:16

quote:
Originally posted by hspatil31

Hello Friend,

After closing cursors, I am using following code for checking If any error occurs. If no error occurs it will return 0 otherwise it should return errror.

My confusion is I think i am putting following code on wrong place because If any error occurs while executing cursor that time is going in If statement if not therefore also it'd going inside IF statement.

I want If any error occurs while executing cursor then It should go in ELSE part.

IF(@@Error=0)
BEGIN
RETURN 0
END
ELSE
BEGIN
print 'Error at ' + convert(varchar,getdate(),9);
END

Thanks and Regard's
Harish Patil

You wont get error value unless you capture it just after the staement which threw error. Thats why i suggested you to use TRY...CATCH

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-18 : 02:09:00

So..visakh..

you don't agree that they don't need a cursor???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 03:27:59

quote:
Originally posted by X002548

So..visakh..

you don't agree that they don't need a cursor???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

I dont know what they're doing with it so unless I have more idea on it I cant suggest on that. Also OP has not given as bigger picture for us to give some alternate suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 05:31:16

They don't need a cursor but if it's already coded the immediate issue is how to handle errors.
That would be a try catch block by preference or testing @@error immediately after each statement - not once at the end.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-18 : 10:31:24

quote:
Originally posted by nigelrivett

They don't need a cursor but if it's already coded the immediate issue is how to handle errors.
That would be a try catch block by preference or testing @@error immediately after each statement - not once at the end.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

exactly
I also thought that would be the best thing that can be done in first level.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources