By S.Luann
With Sybase 12.5, (SQL Server 2005 is the same), failing of a statement in a batch doesn't necessarily abort the batch. This behavior forces developer to check the running status of each DML statement that makes changes to the database so that to determine whether to commit or rollback a transaction.
In order to investigate this issue, I prepared two tables (List 1 and List 2) and a stored procedure (List 3).
List 1.
CREATE TABLE dbo.test
(
id decimal(38,0) NOT NULL,
name varchar(2048) NOT NULL,
CONSTRAINT test_340193231
FOREIGN KEY (id)
REFERENCES dbo.test2 (id)
)
List 2.
CREATE TABLE dbo.test2
(
id decimal(38,0) NOT NULL,
CONSTRAINT test2_3241931742
PRIMARY KEY NONCLUSTERED (id)
)
List 3.
CREATE PROCEDURE p_test
AS
BEGIN
BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, NULL) -- CAUSE EXCEPTION BECAUSE NAME COLUMN IS NOT NULLABLE
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS
COMMIT TRAN
END
In table test2 only one record with id equals to 100.
Executing this stored procedure in List 3 results an exception saying null is not allowed, and no records has been inserted. No transaction voilation happend.
Then run List 4, It causes an exception saying referencial integrity problem, and there is a record inserted to table test. It violates the transaction integrity! This behavior tells that a run-time error doesn't stop the batch running. To test this behavior, I execute List 5. which uses a parameter rather than a static value, It inserted a record and violates the transaction as well.
List 4.
CREATE PROCEDURE p_test
AS
BEGIN
BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(99, 'Non null value') -- CAUSE EXCEPTION BECAUSE ONLY 100 WON'T CAUSE REFERENCIAL VIOLATION
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS
COMMIT TRAN
END
List 5
CREATE PROCEDURE p_test
(
@name VARCHAR(50) = NULL
)
AS
BEGIN
BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, @name) -- CAUSE EXCEPTION if the parameter is passing null
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS
COMMIT TRAN
END
GO
EXECUTE p_test
GO
Why in the first case it doesn't violate the transaction? I traced step by step and found the exception in the first case comes from compile-time, because it doesn't even run into the procedure. It is understandable though that a static value will be checked out if violating any rules and constraints.
The Conclusion
To get the transaction work, I check the error status after each DML statement which makes changes to the database. See List 6.
List 6.
CREATE PROCEDURE p_test
(
@name VARCHAR(50) = NULL
)
AS
BEGIN
BEGIN TRAN
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, @name) -- CAUSE EXCEPTION if the parameter is passing null
IF(@@error<>0) GOTO fail
INSERT INTO dbo.test
(
id,
name
)
VALUES
(100, 'THIS WORKS') -- THIS ONE WORKS
IF(@@error<>0) GOTO fail
COMMIT TRAN
RETURN(0)
fail:
ROLLBACK TRAN
RETURN(1)
END
GO
EXECUTE p_test
GO
EXECUTE p_test 'any name'
GO