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

Need help with While loop

Hi,

I am trying to implement TRY..CATCH in the below script.

BEGIN TRAN

-- Initialize Variables
SET @Counter = 1
SET @MaxRows = (SELECT COUNT(*) FROM #tblPhysiciansToMerge)

WHILE @Counter <= @MaxRows
	BEGIN
		
		SET @Remarks  = ''
		
		SELECT @PhysicianId_From = p.PhysicianId_From,
						@PhysicianId_To = p.PhysicianId_To,
						@DemogValueType = p.DemogValueType
		FROM #tblPhysiciansToMerge p
		WHERE p.RowId	= @Counter	
	
		-- Merge the Physicians
		EXEC sp_PhysicianMerge @PhysicianId_From, @PhysicianId_To, @MergeDups, 'N'
		SELECT @Error = @@Error
		
		IF @Error != 0
			BEGIN
				SET @ErrorMsg = 'Error occurred calling: sp_PhysicianMerge!  Error Number: ' + convert(varchar(20), @Error)
				SET @ErrorMessage = ERROR_MESSAGE()
				BREAK;
			END
		
		SET @Remarks  = 'MergeDups: ' + @MergeDups
		
		-- Record the Merge.
		INSERT INTO [dbo].[tblPhysiciansMerged]
					   ([PhysicianID_From]
					   ,[PhysicianID_To]
					   ,[DemogValueType]
					   ,[Remarks])
		VALUES (@PhysicianId_From, @PhysicianId_To, @DemogValueType, @Remarks)
			
		SELECT @Error = @@Error
		
		IF @Error != 0
			BEGIN
				SET @ErrorMsg = 'INSERT to tblPhysiciansMerged FAILED!  Error Number: ' + convert(varchar(20), @Error)
				SET @ErrorMessage = ERROR_MESSAGE()
				BREAK;
			END
			
		SELECT @Counter = @Counter + 1
	END

IF @Error != 0 
	BEGIN
		IF @@TranCount > 0 ROLLBACK TRAN
		RAISERROR('ME Number Physician Merge Problem!  Details follow: %d 
		                                               Error Message : %s', 16, 1, @ErrorMsg, @ErrorMessage)
	
	END 
ELSE
	BEGIN
		IF @@TranCount > 0 COMMIT TRAN	
	END

Open in new window


I have come up with the below script. Please let me know if this works... Also please suggest if I need to make any changes.

BEGIN TRAN trn1

BEGIN TRY

SET @Counter = 1
SET @MaxRows = (SELECT COUNT(*) FROM #tblPhysiciansToMerge)

WHILE @Counter <= @MaxRows
	BEGIN
		SET @Remarks  = ''
		
		SELECT @PhysicianId_From = p.PhysicianId_From,
						@PhysicianId_To = p.PhysicianId_To,
						@DemogValueType = p.DemogValueType
		FROM #tblPhysiciansToMerge p
		WHERE p.RowId	= @Counter	
	BEGIN TRY
		-- Merge the Physicians
		EXEC sp_PhysicianMerge @PhysicianId_From, @PhysicianId_To, @MergeDups, 'N'
		SELECT @Error = @@Error
	END TRY
	BEGIN CATCH	
		IF @Error != 0
			BEGIN
				SET @ErrorMsg = 'Error occurred calling: sp_PhysicianMerge!  Error Number: ' + convert(varchar(20), @Error)
				SET @ErrorMessage = ERROR_MESSAGE() 

				BREAK;
			END
		
		SET @Remarks  = 'MergeDups: ' + @MergeDups
		END CATCH
		-- Record the Merge.
		BEGIN TRY
		INSERT INTO [dbo].[tblPhysiciansMerged]
					   ([PhysicianID_From]
					   ,[PhysicianID_To]
					   ,[DemogValueType]
					   ,[Remarks])
		VALUES (@PhysicianId_From, @PhysicianId_To, @DemogValueType, @Remarks)
			
		SELECT @Error = @@Error
		END TRY
		
		BEGIN CATCH
		IF @Error != 0
			BEGIN
				SET @ErrorMsg = 'INSERT to tblPhysiciansMerged FAILED!  Error Number: ' + convert(varchar(20), @Error)
				SET @ErrorMessage = ERROR_MESSAGE()
				BREAK;
			END
			END CATCH
		SELECT @Counter = @Counter + 1
	END
	COMMIT TRAN trn1
		PRINT 'Success at merging'
END TRY

BEGIN CATCH
--IF @Error != 0 
	
		IF @@TranCount > 0 ROLLBACK TRAN trn1
		RAISERROR('Physician Merge Problem! Failed at trn1  Details follow: %d 
		                                     Error Message : %s', 16, 1, @ErrorMsg, @ErrorMessage)
	
END CATCH
--ELSE
--	BEGIN
--		IF @@TranCount > 0 COMMIT TRAN trn1
--		PRINT 'Success at merging'
--	END

Open in new window



Thanks in advance!!!
0
ravichand-sql
Asked:
ravichand-sql
  • 2
1 Solution
 
SharathData EngineerCommented:
>> Please let me know if this works..

You need to test this and let us know if not working. Anyone here will help you to fix the issue.

I did not see what's the question here. Please explain what are you trying to achieve?
0
 
ravichand-sqlAuthor Commented:
In the first script, there is a "while loop". I am implementing TRY.. CATCH in the while loop  in the second script. And I was not sure about the semantics. I wanted to make sure I am doing it right.
0
 
SharathData EngineerCommented:
The TRY-CATCH blocks you have placed looks good. Execute it and let us know your findings.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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