CODEWITHSUNDEEP
sql-serverstored-procedurestransactionstry-catch
kashif
kashif

Reputation: 3834

Using Transaction

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

Upvotes: 1

Views: 146

Answers (3)

Parag Meshram
Parag Meshram

Reputation: 8511

Best Practice for Writing SQL Server Stored Procedure with Transaction -

  1. Enclose withing TRY..CATCH block
  2. Check for @@TRANCOUNT and ROLLBACK Transaction on ERROR
  3. 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.

Upvotes: 1

Pete Carter
Pete Carter

Reputation: 2731

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

Upvotes: 1

Kermit
Kermit

Reputation: 34055

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:

Upvotes: 2

Related Questions