Using Transaction

Question

How do I alter the following procedure in such a way that if insert statement is not successfully executed because of PrimaryKey or something the Delete Statement must also not be executed and further more it should generate an error message that I would Write myself.

CREATE PROCEDURE [dbo].[ReAdmissionInsert]
@GRNo varchar(4),
@ClassId numeric(2),
@FacultyId numeric(1),
@Section varchar(1),
@SessionId numeric(1)
AS
begin
insert into ReAdmissionDtl(GRNo,ClassId,FacultyId,Section,SessionId) values(@GRNo,@ClassId,@FacultyId,@Section,@SessionId)
delete from Discharge where GRNo = @GRNo
end

score 2 · Answer 1 · edited May 23 '17 at 11:48

You use BEGIN TRAN & COMMIT to create a transaction that will be rolled back if your INSERT or DELETE fails:

CREATE PROCEDURE [dbo].[Readmissioninsert] @GRNo      VARCHAR(4),
                                           @ClassId   NUMERIC(2),
                                           @FacultyId NUMERIC(1),
                                           @Section   VARCHAR(1),
                                           @SessionId NUMERIC(1)
AS
  BEGIN
      BEGIN TRAN  --<= Starting point of transaction

      INSERT INTO readmissiondtl
                  (grno,
                   classid,
                   facultyid,
                   section,
                   sessionid)
      VALUES     (@GRNo,
                  @ClassId,
                  @FacultyId,
                  @Section,
                  @SessionId)

      DELETE FROM discharge
      WHERE  grno = @GRNo

      COMMIT --<= End point of transaction
  END

Documentation

You can use a TRY CATCH for the error message:

I have applied your code but this is not working. when i enter duplicate values through insert statement it gives error of violation of primary key while record from my discharge table for the given GRNo is yet being deleted — kashif, Oct 22 '12 at 17:36
@kashif I don't see why the transaction would not rollback. I've provided the resource to implement an error message. — Kermit, Oct 22 '12 at 17:51

score 1 · Accepted Answer · edited Oct 22 '12 at 17:55

1

Use a transaction and a try catch block. Raise your error in the catch block, like so:

CREATE PROCEDURE [dbo].[ReAdmissionInsert]
@GRNo varchar(4),
@ClassId numeric(2),
@FacultyId numeric(1),
@Section varchar(1),
@SessionId numeric(1)
AS
begin
Begin transaction
Begin try
    insert into ReAdmissionDtl(GRNo,ClassId,FacultyId,Section,SessionId)
       values(@GRNo,@ClassId,@FacultyId,@Section,@SessionId)
    delete from Discharge where GRNo = @GRNo
    Commit transaction
End try
Begin catch
    Rollback
    Raiserror(999999,'my message',16,1)
End catch
end

edited Oct 22 '12 at 17:55

Kermit

33,827
13
85
121

answered Oct 22 '12 at 17:40

Pete Carter

2,691
3
23
34

thanks alot it worked... how can i insert values selected from table in error message. like if i want to set my error message like this 'my error message' + select mycolumn from mytable where mycolumnvalue = 'myvalue' now suppose if selected value is myvalue than error message should be like this. 'my error message myvalue' I don't know whether I vave clearified what I want or not. but may be you understand what i am talking about – kashif Oct 22 '12 at 17:49

Parag Meshram · Answer 3 · 2012-10-23T19:45:09.373

Best Practice for Writing SQL Server Stored Procedure with Transaction -

Enclose withing TRY..CATCH block
Check for @@TRANCOUNT and ROLLBACK Transaction on ERROR
RAISE actual ERROR to alarm calling program

Sample -

CREATE PROCEDURE [dbo].[ReAdmissionInsert]
@GRNo varchar(4),
@ClassId numeric(2),
@FacultyId numeric(1),
@Section varchar(1),
@SessionId numeric(1)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY   
    BEGIN TRAN

    insert into ReAdmissionDtl(GRNo,ClassId,FacultyId,Section,SessionId)
       values(@GRNo,@ClassId,@FacultyId,@Section,@SessionId)

    delete from Discharge where GRNo = @GRNo

    COMMIT TRAN     
END TRY    
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

    /*ERROR OCCURED*/  
    DECLARE @ERROR_MESSAGE NVARCHAR(4000);--MESSAGE TEXT           
    DECLARE @ERROR_SEVERITY INT;--SEVERITY           
    DECLARE @ERROR_STATE INT;--STATE        

    SELECT @ERROR_MESSAGE = ERROR_MESSAGE(),        
        @ERROR_SEVERITY = ERROR_SEVERITY(),        
        @ERROR_STATE = ERROR_STATE() 


    /*RETURN ERROR INFORMATION ABOUT THE ORIGINAL ERROR THAT CAUSED
      EXECUTION TO JUMP TO THE CATCH BLOCK.*/           
    RAISERROR (@ERROR_MESSAGE, @ERROR_SEVERITY, @ERROR_STATE)

END CATCH   
END

Notice that @@TRANCOUNT is checked to verify if there is any open transactions and ERROR messages are retained and raised so program will receive SqlException.

Using Transaction

3 Answers3