SQL Transaction

Hi,

I would like to add a commit and rollback transaction to the code below.
Everything I have tried to so far does not seem to work, I think it is because I have multiple action in the code.

Would be grateful for any help.

Thanks

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
   
            
                  Insert Into tbl_M04_CompanyRolesHistory
                        (CompanyRoleName, CompanyRoleDescription,
                        CompanyId, LastModifiedBy, HistoryAction, CompanyRoleId)
                        SELECT CompanyRoleName, CompanyRoleDescription,CompanyID, @ModifiedBy, 'Deleted_text', @CompanyRoleID
                        FROM tbl_M04_CompanyRoles
                        Where CompanyRoleID = @CompanyRoleID
                              
                  DELETE dbo.tbl_M04_CompanyRoles
                  Where CompanyRoleID = @CompanyRoleID
                  
                  DELETE tlnk_M04_CompanyRolePermissions
                  WHERE CompanyRoleID = @CompanyRoleID
                  
                  DELETE tlnk_M04_CompanyRoleUsers
                  WHERE CompanyRoleID = @CompanyRoleID
                  
                  SET @ReturnValue = '1'
            
END
LVL 1
SolugaAsked:
Who is Participating?
 
Habib PourfardConnect With a Mentor Software DeveloperCommented:
try the following code:

BEGIN

    SET NOCOUNT ON;
   
   DECLARE @ReturnValue BIT
   
    BEGIN TRAN TM
    BEGIN TRY

            
        INSERT  INTO tbl_M04_CompanyRolesHistory
                (
                 CompanyRoleName
                ,CompanyRoleDescription
                ,CompanyId
                ,LastModifiedBy
                ,HistoryAction
                ,CompanyRoleId
                )
                SELECT  CompanyRoleName
                       ,CompanyRoleDescription
                       ,CompanyID
                       ,@ModifiedBy
                       ,'Deleted_text'
                       ,@CompanyRoleID
                FROM    tbl_M04_CompanyRoles
                WHERE   CompanyRoleID = @CompanyRoleID
                              
        DELETE  dbo.tbl_M04_CompanyRoles
        WHERE   CompanyRoleID = @CompanyRoleID
                  
        DELETE  tlnk_M04_CompanyRolePermissions
        WHERE   CompanyRoleID = @CompanyRoleID
                  
        DELETE  tlnk_M04_CompanyRoleUsers
        WHERE   CompanyRoleID = @CompanyRoleID
                  

            
        COMMIT TRAN TM
        SET @ReturnValue = 1 ;
    END TRY

    BEGIN CATCH
        ROLLBACK TRAN TM
        SET @ReturnValue = 0 ;
    END CATCH

END

Open in new window

0
 
x-menIT super heroCommented:
where and why you want to "add a commit and rollback"?
0
 
gazzacroweConnect With a Mentor Commented:
all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure

Something like this should work
SET NOCOUNT ON;

DECLARE @Error      VARCHAR(1000)

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

      BEGIN TRANSACTION
      SET @Error = 'Company Role History'
      Insert Into tbl_M04_CompanyRolesHistory
            (CompanyRoleName, CompanyRoleDescription,
            CompanyId, LastModifiedBy, HistoryAction, CompanyRoleId)
            SELECT CompanyRoleName, CompanyRoleDescription,CompanyID, @ModifiedBy, 'Deleted_text', @CompanyRoleID
            FROM tbl_M04_CompanyRoles
            Where CompanyRoleID = @CompanyRoleID
            
      IF @@ERROR <> 0 GOTO FailLabel
                
      SET @Error = 'Delete Company Role '
      DELETE dbo.tbl_M04_CompanyRoles
      Where CompanyRoleID = @CompanyRoleID
      IF @@ERROR <> 0 GOTO FailLabel

      SET @Error = 'Delete Company Role Permission'
      DELETE tlnk_M04_CompanyRolePermissions
      WHERE CompanyRoleID = @CompanyRoleID
      IF @@ERROR <> 0 GOTO FailLabel

      SET @Error = 'Delete Company Role User'
      DELETE tlnk_M04_CompanyRoleUsers
      WHERE CompanyRoleID = @CompanyRoleID
      IF @@ERROR <> 0 GOTO FailLabel
      
      GOTO SuccessLabel
END

FailLabel:
      SET @Error = 'Failed to delete Company Role' + @Error
      IF @@TRANCOUNT > 0  ROLLBACK TRANSACTION
      RAISERROR(@Error, 16, 2)
      RETURN 1

SuccessLabel:
      IF @@TRANCOUNT > 0  COMMIT TRANSACTION
      RETURN 0

GOTO are gennerally bad but I am from SQL2000 which does not have many options, I think Later Versions allow Try Catch to tidy this up
0
 
SolugaAuthor Commented:
Great.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.