Solved

Need help with While loop

Posted on 2013-06-03
3
312 Views
Last Modified: 2013-06-09
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
Comment
Question by:ravichand-sql
  • 2
3 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
>> 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
 

Author Comment

by:ravichand-sql
Comment Utility
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
The TRY-CATCH blocks you have placed looks good. Execute it and let us know your findings.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now