Create Transaction Inside the Stored Procedure-SQL

The Query as Follows:

CREATE TABLE A(ID1 INT IDENTITY,VALUE INT CONSTRAINT PK_A PRIMARY KEY NOT NULL)

CREATE TABLE B(ID2 INT IDENTITY,VALUE2 INT CONSTRAINT PK_B PRIMARY KEY NOT NULL)

CREATE TABLE C(ID3 INT IDENTITY,VALUE3 INT CONSTRAINT PK_C PRIMARY KEY NOT NULL)

Procedure:
CREATE PROCEDURE VALUE_INSERT
(
@FIRST INT=NULL,
@SECOND INT=NULL,
@THIRD INT=NULL)
AS
SET NOCOUNT ON
BEGIN TRAN T1
INSERT INTO A VALUES(@FIRST)
IF(@@ERROR!=0)
BEGIN
ROLLBACK TRAN T1
RETURN
END
INSERT INTO B VALUES(@SECOND)
IF(@@ERROR!=0)
BEGIN
ROLLBACK TRAN T1
RETURN
END
INSERT INTO C VALUES(@THIRD)
IF(@@ERROR!=0)
BEGIN
ROLLBACK TRAN T1
RETURN
END
COMMIT TRAN T1
For the above example I am creating 3 tables. If anywhere the primary key violation occurs then the whole transaction will be Roll backed.

Executing the Procedure:
VALUE_INSERT 1,1,1

VALUE_INSERT-Procedure Name


For the above sp the identity state changed.

In order to create a sequential identity insert u have manually insert the identity value by setting the identity_insert on to the concern table

CREATE PROCEDURE INSERT_VALUE
(
@FIRST INT=NULL,
@SECOND INT=NULL,
@THIRD INT=NULL)
AS
SET NOCOUNT ON
DECLARE @ID1 INT
DECLARE @ID2 INT
DECLARE @ID3 INT
SELECT @ID1=COUNT_BIG(ID1)+1 FROM A
SELECT @ID2=COUNT_BIG(ID2)+1 FROM B
SELECT @ID3=COUNT_BIG(ID3)+1 FROM C BEGIN TRAN T1 BEGIN SET IDENTITY_INSERT A ON
INSERT INTO A(ID1,VALUE) VALUES(@ID1,@FIRST)
IF(@@ERROR!=0)
BEGIN
ROLLBACK TRAN T1
RETURN
END
SET IDENTITY_INSERT A OFF
END

BEGIN
SET IDENTITY_INSERT B ON
INSERT INTO B(ID2,VALUE2) VALUES(@ID2,@SECOND)
IF(@@ERROR!=0)
BEGIN
ROLLBACK TRAN T1
RETURN
END
SET IDENTITY_INSERT B OFF
END
BEGIN
SET IDENTITY_INSERT C ON
INSERT INTO C(ID3,VALUE3) VALUES(@ID3,@THIRD)
IF(@@ERROR!=0)
BEGIN
ROLLBACK TRAN T1
RETURN
END
SET IDENTITY_INSERT C OFF
END
COMMIT TRAN T1