Error Handling from a Main Sp - SQL Server Forums

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

Transact-SQL (2000)

Error Handling from a Main Sp

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-21 : 14:34:56

I have 3 stored procedure
1. Main Sp
2. Module Sp
3. Detail level SP

Eg:
usp_populate_ivoc is the main SP

I have module sp like usp_emp_info which is called inside the main Sp

I have different Sps called inside the Sp usp_emp_info
1. usp_emp_i
2. usp_emp_address_i
3. usp_emp_contact_i
4. usp_emp_u
5. usp_emp_address_u
6. usp_emp_contact_u

Here my issue is If one of the usp_emp_address_i fails I want to rollback everything and capture the error in the
tbl_error_log table

--Called inside usp_emp_info
EXECUTE @error_int = usp_emp_address_i @emp_number
-- Error Handling Section
IF @error_int <> 0
BEGIN
RETURN @error_int
END

Here I will get back my error to my main Sp

EXECUTE @error_int = usp_emp_info @emp_number
-- Error Handling Section
IF @error_int <> 0
BEGIN
RETURN @error_int
END

But now how will I capture which Sp has failed because in this case it gives you only the error

where should I use a error handler to get which Sp caused issue

Error_Handler_Section:

IF (@Error_int <> 0 )

BEGIN
SET @Err_Msg_vchr =(SELECT DESCRIPTION FROM master..sysmessages WHERE error = @Error_int)
EXEC dbo.usp_MrErrorHandler @ErrorNo =
@Error_int,
@ErrorMsg = @Err_Msg_vchr,
@TableName = @Ref_Table_Name_vchr,
@CallingSPName = @SP_Name_vchr,
@Parameters = @Parameters_vchr,
@ModuleName = @Module_Name_vchr,
@ErrorNotes = @Error_Notes_vchr,
@ComputerNm = @Computer_Name_vchr
RETURN @Error_int
END

ELSE

BEGIN
RETURN 0
END

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-21 : 15:11:54

Can this be used as a solution

DECLARE @error_sp INT
EXECUTE @error_int = usp_emp_info @emp_number,@error_sp OUTPUT
-- Error Handling Section
IF @error_int <> 0
BEGIN
SET @error_sp = 1
RETURN @error_int

AND then use something like this in the module SP
IF (@Error_int <> 0 )
BEGIN
IF error_sp=1
BEGIN
SET @SP_Name_vchr='usp_emp_i'
SET @Ref_Table_Name_vchr ='tbl_emp'
SET @Error_Notes_vchr ='Error in Insertion'
END
IF error_sp=0
BEGIN
SET @SP_Name_vchr='usp_emp_u'
SET @Ref_Table_Name_vchr ='tbl_emp'
SET @Error_Notes_vchr ='Error in Updation'
END
GOTO Error_Handler_Section
END

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-07-21 : 20:20:06

Will lots of SP OUTPUT hit performance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-22 : 02:33:16

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources