• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

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
0
Soluga
Asked:
Soluga
2 Solutions
 
x-menIT super heroCommented:
where and why you want to "add a commit and rollback"?
0
 
gazzacroweCommented:
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
 
Habib PourfardCommented:
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
 
SolugaAuthor Commented:
Great.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now