Error Handling in Stored Procedures

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 in Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-11 : 07:16:00

Vishrant writes "Hi,
Please go through the following Stored Procedure.

Create Procedure stp_Test
@ErrorNo int Output,
@Link int

as

Select * from datSurvey Where Link = @Link

If @@Error <> 0
Set @ErrorNo = @@Error

This is my Stored Procedure. I'm calling this stored procedure from VB. What i want to know is whether if an error occurs in the select statement the next line will be executed.
Because when i call this from VB and passing an invalid value to @Link so that an error occurs, it is not giving me the ErrorNo which i had added to the parameters collection in VB. I'm getting a run time error. Could you please clarify.

Regards,
Vishrant"

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-11 : 09:24:57

I would imagin that @@Error in this case will always be 0. I believe you're trying to determine if a business logic error occurred where by a result set of 0 is an "error". These are 2 different things. You need to check out @@ROWCOUNT. If that's 0, then you have your problem. Also I would code error handling like this:

SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error

If @Error_Out <> 0
BEGIN
Select @Error_Loc = 17
Select @Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

If @Result_Count = 0
BEGIN
SELECT @Error_Loc = 18
SELECT @Error_Message = 'No Batch ID Obtained to process file 1. Check the status of the table '
+ ' BATCH_CNTL'
SELECT @Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END

Load_Ledger_Init_sp_Exit:

SET NOCOUNT OFF

return @Comp_Code

Load_Ledger_Init_sp_Error:
Rollback TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message, @Load_Ind = 'N'

If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
+ ' @@ERROR: ' + RTRIM(Convert(char(6),error))
+ ' Severity: ' + RTRIM(Convert(char(3),severity))
+ ' Message: ' + RTRIM(description)
From master..sysmessages
Where error = @error_out)
END
If @Error_Type = 50002
BEGIN
Select @Error_Message = 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
+ ' Severity: UserLevel '
+ ' Message: ' + RTRIM(@Error_Message)
END

Exec(@Command_String)

DECLARE USER_MSG CURSOR READ_ONLY FOR
select distinct user_id from user_work_profile(NoLock)
Where Work_Type = 'A'
-- Where User_Id = 'x057117'
OPEN USER_MSG

FETCH NEXT FROM USER_MSG
INTO @User_Id

WHILE @@FETCH_STATUS = 0

BEGIN
Select @Command_String = 'Exec master..xp_cmdshell "NET SEND ' + @User_Id + ' TaxReconDB Load Failed", no_output '
Select @Command_String
Exec (@Command_String)

FETCH NEXT FROM USER_MSG
INTO @User_Id
END

CLOSE USER_MSG
DEALLOCATE USER_MSG

RAISERROR @Error_Type @Error_Message

GOTO Load_Ledger_Init_sp_Exit

Brett

8-)

Edited by - x002548 on 04/11/2003 09:25:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources