May 12, 2013 at 7:01 am
IF OBJECT_ID('test_parent_table') IS NOT NULL
DROP TABLE test_parent_table
CREATE TABLE test_parent_table
(idINT,
nameVARCHAR(30)
)
GO
IF OBJECT_ID('DataError') IS NOT NULL
DROP TABLE DataError
CREATE TABLE DataError
(ErrorNoINT,
ErrorDescVARCHAR(1000),
ErrorProcVARCHAR(100),
ErrorLineNumberINT,
ErrorDateTimeDATETIME2(7)
)
GO
IF OBJECT_ID('ThrowError') IS NOT NULL
DROP PROCEDURE ThrowError
GO
CREATE PROCEDURE ThrowError
AS
BEGIN
DECLARE@errorNoINT,
@errorDescVARCHAR(1000),
@errorProcVARCHAR(100),
@errorLineNumberINT,
@errorDateTimeDATETIME2(7)
SELECT@errorNo=ERROR_NUMBER(),
@errorDesc=ERROR_MESSAGE(),
@errorProc=ERROR_PROCEDURE(),
@errorLineNumber=ERROR_LINE(),
@errorDateTime=GETDATE()
DECLARE @id INT = XACT_STATE()
IF( @errorNo < 50000)
BEGIN
IF XACT_STATE() > 0 ROLLBACK
INSERT INTO DataError
SELECT@errorNo,
@errorDesc,
@errorProc,
@errorLineNumber,
@errorDateTime
--RAISERROR(@errorDesc,16,1);
--RETURN 1
END
ELSE
BEGIN
RAISERROR(@errorDesc,16,1);
END
END;
GO
IF OBJECT_ID('test_child') IS NOT NULL
DROP PROCEDURE test_child
GO
CREATE PROCEDURE test_child
(@idINT,
@nameVARCHAR(50)
)
AS
BEGIN
BEGIN TRY
INSERT INTO test_parent_table
SELECT@id,
@name
SELECT1/0
RETURN 0
END TRY
BEGIN CATCH
EXEC ThrowError
RETURN 1
END CATCH
END
GO
IF OBJECT_ID('test_parent') IS NOT NULL
DROP PROCEDURE test_parent
GO
CREATE PROCEDURE test_parent
(@idINT,
@nameVARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO test_parent_table
VALUES( 1,'Harry')
EXEC test_child 2,'Kristena'
SELECT 1/0
INSERT INTO test_parent_table
VALUES( 3,'Chris')
IF( @@TRANCOUNT > 0)
COMMIT
RETURN 0
END TRY
BEGIN CATCH
EXEC ThrowError
END CATCH
END
GO
/* Test cases
EXEC test_parent 0,'test'
SELECT * FROM test_parent_table
SELECT * FROM DataError
*/
WHEn I run the test case above , the output in DataError is given below:-
ErrorDesc
1 Row :- Divide by zero error encountered.
2 Row :- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Here second row is not supposed to come, this is sucking. Please help!
Regards
Ashok
May 12, 2013 at 7:45 am
AND ANSWER IS
IF OBJECT_ID('test_parent_table') IS NOT NULL
DROP TABLE test_parent_table
CREATE TABLE test_parent_table
(idINT,
nameVARCHAR(30)
)
GO
IF OBJECT_ID('DataError') IS NOT NULL
DROP TABLE DataError
CREATE TABLE DataError
(ErrorNoINT,
ErrorDescVARCHAR(1000),
ErrorProcVARCHAR(100),
ErrorLineNumberINT,
ErrorDateTimeDATETIME2(7)
)
GO
IF OBJECT_ID('ThrowError') IS NOT NULL
DROP PROCEDURE ThrowError
GO
CREATE PROCEDURE ThrowError
AS
BEGIN
DECLARE@errorNoINT,
@errorDescVARCHAR(1000),
@errorProcVARCHAR(100),
@errorLineNumberINT,
@errorDateTimeDATETIME2(7)
SELECT@errorNo=ERROR_NUMBER(),
@errorDesc=ERROR_MESSAGE(),
@errorProc=ERROR_PROCEDURE(),
@errorLineNumber=ERROR_LINE(),
@errorDateTime=GETDATE()
DECLARE @id INT = XACT_STATE()
IF( @errorNo < 50000)
BEGIN
IF XACT_STATE() <> 0
BEGIN
ROLLBACK
DECLARE @id34 INT = XACT_STATE()
END
INSERT INTO DataError
SELECT@errorNo,
@errorDesc,
@errorProc,
@errorLineNumber,
@errorDateTime
RAISERROR(@errorDesc,16,1);
--RETURN 1
END
ELSE
BEGIN
RAISERROR(@errorDesc,16,1);
END
END;
GO
IF OBJECT_ID('test_child') IS NOT NULL
DROP PROCEDURE test_child
GO
CREATE PROCEDURE test_child
(@idINT,
@nameVARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO test_parent_table
SELECT@id,
@name
SELECT1/0
DECLARE @id11 INT = @@TRANCOUNT
IF( @@TRANCOUNT > 0)
COMMIT
RETURN 0
END TRY
BEGIN CATCH
DECLARE @id1 INT = XACT_STATE()
DECLARE @id12 INT = @@TRANCOUNT
IF XACT_STATE() <> 0 ROLLBACK
EXEC ThrowError
END CATCH
END
GO
IF OBJECT_ID('test_parent') IS NOT NULL
DROP PROCEDURE test_parent
GO
/* Test cases
EXEC test_parent 0,'test'
SELECT * FROM test_parent_table
SELECT * FROM DataError
*/
CREATE PROCEDURE test_parent
(@idINT,
@nameVARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO test_parent_table
VALUES( 1,'Harry')
EXEC test_child 2,'Kristena'
SELECT 1/0
INSERT INTO test_parent_table
VALUES( 3,'Chris')
DECLARE @id11 INT = @@TRANCOUNT
IF( @@TRANCOUNT > 0)
COMMIT
RETURN 0
END TRY
BEGIN CATCH
DECLARE @id1 INT = XACT_STATE()
DECLARE @id12 INT = @@TRANCOUNT
IF XACT_STATE() <> 0 ROLLBACK
EXEC ThrowError
END CATCH
END
GO
/* Test cases
EXEC test_parent 0,'test'
SELECT * FROM test_parent_table
SELECT * FROM DataError
*/
It is now perfect : I am sorry to bother you guys..:-P:hehe:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply