Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with While loop

Posted on 2013-06-03
3
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

670 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