none
How do I wrap my Stored Procedure in a Transaction RRS feed

  • Question

  • Below is a SP I just created, however if for any reason it fails to truncate 1 of 1000 tables half way through I don't want to leave the tables in an inconsistent state. Someone mentioned including a transaction but just unsure where and how to do this.

    Basically I either want all tables truncated or none of them.

    USE [HealthBI]
    GO
    
    CREATE PROCEDURE [dbo].[HUTH_spTruncateLoaderModelTables]
    	(
    		@LoaderName nvarchar(255)
    	)
    	
    AS
    BEGIN
    
    	DECLARE @TruncateSql AS varchar(max)
    	DECLARE @TableName AS varchar(max)
    	
    	IF (SELECT CAST([__tblDTSTemplate].[DateLastRun] AS date) FROM [dbo].[__tblDTSTemplate] WHERE [__tblDTSTemplate].[Description] = @LoaderName) = '1 Jan 1753'
    	BEGIN	
    
    		DECLARE csrTable CURSOR FOR
    			SELECT [__tblDataLoader].[TargetTablePrefix]+[__tblDataLoaderTable].[TargetTable]
    			FROM [dbo].[__tblDataLoaderTable]
    			LEFT JOIN [dbo].[__tblDataLoader] 
    				ON [__tblDataLoader].[DataLoaderID] = [__tblDataLoaderTable].[DataLoaderID]
    			WHERE [__tblDataLoader].[DataLoaderName] = @LoaderName
    
    			OPEN csrTable
    
    				FETCH NEXT FROM csrTable INTO @TableName
    
    				WHILE  @@fetch_status = 0
    				BEGIN
    
    					SET @TruncateSql = 'TRUNCATE TABLE [dbo].[' + @TableName  + ']'
    
    					EXEC(@TruncateSql)
    					--PRINT(@TruncateSql)
    
    					FETCH NEXT FROM csrTable INTO @TableName 
    				END
    			CLOSE csrTable
    		DEALLOCATE csrTable
    	END
    END
    
    GO


    • Edited by SimonKEvans Wednesday, January 22, 2020 1:43 PM spelling mistake
    Wednesday, January 22, 2020 1:25 PM

Answers

All replies

  • First off all add to the procedure SET XACT_ABORT ON option

    Then 

    BEGIN TRANSACTION
    BEGIN TRY

    <your code here >

    END TRY
    BEGIN CATCH
    IF @@trancount > 0 ROLLBACK TRANSACTION
    END CATCH


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 22, 2020 3:01 PM
    Answerer
  • I have now modified this and looking to add RAISEERROR just after the ROLLBACK TRANSACTION, never used this before so dont fully understand what its doing, am reading up on it as I speak.

    ALTER PROCEDURE [dbo].[HUTH_spTruncateLoaderModelTables]
           (
                  @LoaderName nvarchar(255)
           )
    AS
    
    BEGIN
    
    	DECLARE @TruncateSql AS varchar(max)
    	DECLARE @TableName AS varchar(max)
           
    	IF (SELECT CAST([__tblDTSTemplate].[DateLastRun] AS date) FROM [dbo].[__tblDTSTemplate] WHERE [__tblDTSTemplate].[Description] = @LoaderName) = '1 Jan 1753'
    	BEGIN  
    
    		DECLARE csrTable CURSOR FOR
    			SELECT [__tblDataLoader].[TargetTablePrefix]+[__tblDataLoaderTable].[TargetTable]
    			FROM [dbo].[__tblDataLoaderTable]
    			LEFT JOIN [dbo].[__tblDataLoader]
    				ON [__tblDataLoader].[DataLoaderID] = [__tblDataLoaderTable].[DataLoaderID]
    			WHERE [__tblDataLoader].[DataLoaderName] = @LoaderName
    
    			OPEN csrTable
    
    				FETCH NEXT FROM csrTable INTO @TableName
    					
    				BEGIN TRY	
    
    					BEGIN TRANSACTION
    				
    						WHILE  @@fetch_status = 0
    						BEGIN
    
    							SET @TruncateSql = 'TRUNCATE TABLE [dbo].[' + @TableName  + ']'
    
    							EXEC(@TruncateSql)
    							--PRINT(@TruncateSql)
    
    							FETCH NEXT FROM csrTable INTO @TableName 
    						END
    					COMMIT TRANSACTION
    				END TRY
    
    				BEGIN CATCH
    
    					IF @@trancount > 0 
    						ROLLBACK TRANSACTION
    				END CATCH          
    			CLOSE csrTable
    		DEALLOCATE csrTable
    	END
    END
    
    GO

    Thursday, January 23, 2020 8:14 AM
  • You can use ERROR_MESSAGE()  And /or ERROR_NUMBER()

    OR

    BEGIN CATCH
        DECLARE  @ERROR_MESSAGE NVARCHAR(2048) = ERROR_MESSAGE()
        RAISERROR (@ERROR_MESSAGE,16,16)
    END CATCH  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Thursday, January 23, 2020 9:39 AM
    Answerer
  • BEGIN CATCH

        DECLARE  @ERROR_MESSAGE NVARCHAR(2048) = ERROR_MESSAGE()
        RAISERROR (@ERROR_MESSAGE,16,16)
    END CATCH  

    In currently supported SQL Server versions (2012 and later), I suggest THROW instead of RAISERROR to rethrow the original error.

    BEGIN CATCH
    
        IF @@trancount > 0 ROLLBACK;
        THROW;
    
    END CATCH    


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by SimonKEvans Thursday, January 23, 2020 3:24 PM
    Thursday, January 23, 2020 12:06 PM