Create SP with transaction

USE DB NAME SET TRANSACTION ISOLATION LEVEL SERIALIZABLE --Declare Variables --================== Declare @ReleaseRef as integer= 3053 -- our change ref from spreadsheet Declare @Trackref as integer=290147 Declare @ClientRef as integer= 290147 -- incident or work request number Declare @ChangeDesc as varchar(80)='TFS11807 to update YOA' Declare @Env as varchar(4)='V4 Prod' Declare @ReRunnable as varchar ='Y' Declare @RunId int /****************************************/ DECLARE @RecCount INT DECLARE @Msg VARCHAR(200) DECLARE @SuccessInd VARCHAR(1) DECLARE @userid nVARCHAR(50) DECLARE @UpdDate DATETIME DECLARE @Stage VARCHAR(30) SET @Stage = '' SET @Msg = 'Script run incomplete due to errors.' SET @userid = 'DataFix ' SET @UpdDate = GETDATE() SET @RecCount = 0 SET @SuccessInd = 'N' DECLARE @EndTime datetime, @Error int, @StartTime datetime, @sql varchar(1000) SELECT @Error = 0 --Start Procedure --================ SELECT @StartTime = GETDATE() PRINT 'Start Time' PRINT @StartTime SET NOCOUNT OFF BEGIN TRY EXEC sbs_LogCCRun @ReleaseRef, @Trackref, @ClientRef, @ChangeDesc , @Env, @ReRunnable , @RunId OUTPUT IF ISNULL(@RunId,0) <= 0 BEGIN RAISERROR('Error occurred updating the ChangeControlLog',16,1) END END TRY BEGIN CATCH SET @Error = ISNULL(@Error,'') + ISNULL(ERROR_MESSAGE(),'') + ' - ' + CAST(@@Error AS CHAR) PRINT 'Errors Occurred - ' + CONVERT(char, GetDate(), 120) + ' : ' + ISNULL(@Error,'') GOTO Finish END CATCH BEGIN TRAN BEGIN TRY SET @Stage = 'Create archive table' IF OBJECT_ID('Datafixes.dbo.cc3053') IS NULL BEGIN PRINT 'Creating archive tables' CREATE TABLE Datafixes.dbo.cc3053Policy ( [RunId] [int], [PolicyId] [int] NULL, [PrevYOA] int, [UpdYOA] int, [LastUpd] [smalldatetime] NULL, [UpdBy] [nvarchar](50) NULL, --------------------------------------------------- SourceDB varchar(150) ,SourceServer varchar(100) ,HelpdeskRef varchar(20) ,ScriptRunDate datetime ) END IF OBJECT_ID('Datafixes.dbo.cc3053StatsHeader') IS NULL BEGIN PRINT 'Creating archive tables' CREATE TABLE Datafixes.dbo.cc3053StatsHeader ( [RunId] [int], [StatsHeaderId] [int] NULL, [PolicyId] [int] NULL, [PrevYOA] int, [UpdYOA] int, --------------------------------------------------- SourceDB varchar(150) ,SourceServer varchar(100) ,HelpdeskRef varchar(20) ,ScriptRunDate datetime ) END --main processing DECLARE @PolicyLineId int DECLARE @PolicyId int DECLARE @PrevYOA int DECLARE @NewYOA int DECLARE @Upd bit -- populate staging table (paste output from preaparatory script here). insert into datafixes.dbo.[TFS11807StgTable] ([PolicyId], [PrevYOA], [NewYOA]) values (552055, 2014, 2013) -- WRK290147 insert into datafixes.dbo.[TFS11807StgTable] ([PolicyId], [PrevYOA], [NewYOA]) values (552058, 2014, 2013) -- WRK290147 DECLARE DataCursor CURSOR FOR SELECT PolicyId, PrevYOA, NewYOA FROM Datafixes.dbo.TFS11807StgTable WHERE Errors IS NULL AND ISNULL(ProcessedInd,'N')<>'Y' OPEN DataCursor FETCH NEXT FROM DataCursor INTO @PolicyId, @PrevYOA, @NewYOA WHILE @@FETCH_STATUS = 0 BEGIN SET @Upd = 1 -- Check new YOA is valid IF @PrevYOA NOT IN ( SELECT YOA from policy where policyid=@PolicyId ) BEGIN UPDATE Datafixes.dbo.TFS11807StgTable SET Errors = isnull(Errors,'') + 'PrevYOA does not match the YOA on the Policy', ProcessedInd = 'Y', SourceDB=db_name(), SourceServer=@@servername, UserId=@UserId, ScriptRunDate=@UpdDate WHERE PolicyId = @PolicyId AND PrevYOA=@PrevYOA and NewYOA = @NewYOA AND ISNULL(ProcessedInd,'N')<>'Y' SET @Upd = 0 END -- Check new combination is valid IF NOT EXISTS (select 1 from UWLimit u inner join ReportingClass r on u.MajorClassCode = r.Class1 and u.MinorClassCode = r.Class2 and u.Class = r.Class3 and u.ClassType = r.Class4 and u.ProducingTeam = r.ProducingTeam and u.YOA = r.PIMYear inner join ApplicationUser a on a.ApplicationUserId = u.ApplicationUserId inner join policy p on p.UW = a.UserInitials and p.Class1 = r.Class1 and p.Class2 = r.Class2 and p.Class3 = r.Class3 and p.Class4 = r.class4 and @NewYOA = u.YOA inner join synd s on s.SyndId = u.SyndId and s.SyndNo = r.Synd where p.policyid=@policyId) BEGIN UPDATE Datafixes.dbo.TFS11807StgTable SET Errors = isnull(Errors,'') + 'Invalid combination for the Policy', ProcessedInd = 'Y', SourceDB=db_name(), SourceServer=@@servername, UserId=@UserId, ScriptRunDate=@UpdDate WHERE PolicyId = @PolicyId AND PrevYOA=@PrevYOA and NewYOA = @NewYOA AND ISNULL(ProcessedInd,'N')<>'Y' SET @Upd = 0 END UPDATE Policy SET YOA = @NewYOA, LastUpd = @UpdDate, UpdBy = @UserId OUTPUT @RunId, inserted.PolicyId, deleted.YOA, inserted.YOA, deleted.LastUpd, deleted.UpdBy, db_name(), @@servername, @UserId, @UpdDate INTO Datafixes.dbo.cc3053Policy WHERE PolicyId = @PolicyId AND @Upd = 1 IF EXISTS(SELECT 1 FROM dbo.PolicyLine WHERE PolicyId = @PolicyId AND LineStatus = 'written' AND ISNULL(DelDate, 0) = 0 ) BEGIN UPDATE StatsHeader SET YOA=@NewYOA OUTPUT @RunId, inserted.StatsHeaderID, inserted.PolicyId, deleted.YOA, inserted.YOA, db_name(), @@servername, @UserId, @UpdDate INTO Datafixes.dbo.cc3053StatsHeader WHERE PolicyId = @PolicyId AND @Upd = 1 END UPDATE Datafixes.dbo.TFS11807StgTable SET ProcessedInd = 'Y', Errors = isnull(Errors, '') + 'No Errors', SourceDB=db_name(), SourceServer=@@servername, UserId=@UserId, ScriptRunDate=@UpdDate WHERE PolicyId = @PolicyId AND PrevYOA=@PrevYOA and NewYOA = @NewYOA AND Errors IS NULL AND ISNULL(ProcessedInd,'N')<>'Y' FETCH NEXT FROM DataCursor INTO @PolicyID, @PrevYOA, @NewYOA END CLOSE DataCursor DEALLOCATE DataCursor ----------------------------------> SET @STAGE = 'COMMIT & COUNTS' COMMIT TRAN SELECT @Msg = 'Transaction completed successfully', @SuccessInd = 'Y', @RecCount = ISNULL( (SELECT COUNT(*) FROM Datafixes.dbo.TFS11807StgTable WHERE ScriptRunDate = @UpdDate),0) PRINT @Msg END TRY BEGIN CATCH IF @@TRANCOUNT > 0 --Beginning transaction increments this by 1, commiting and rolling back decrements by 1 ROLLBACK TRAN SELECT @Msg = 'Error: ' + ERROR_MESSAGE() + 'Line ' + CAST(ERROR_LINE() AS varchar(4)), @SuccessInd = 'N' PRINT @Msg PRINT GETDATE() END CATCH UPDATE ChangeControlLog SET NumTransUpdated = @RecCount ,RunDateTime = getdate() ,Comments = @msg ,SuccessInd = @SuccessInd WHERE RecNum = @RunId SELECT @EndTime = GETDATE() Finish: PRINT 'Finish Time' PRINT @EndTime