Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with While loop

Posted on 2013-06-03
3
Medium Priority
?
340 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 41

Expert Comment

by:Sharath
ID: 39217531
>> 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
ID: 39220298
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 41

Accepted Solution

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 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