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
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