Solved

Need help with While loop

Posted on 2013-06-03
3
323 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 500 total points
ID: 39220376
The TRY-CATCH blocks you have placed looks good. Execute it and let us know your findings.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 service pack updates 5 46
Logical Operator should return Integer value in SSIS 9 39
Regarding Disk IO 3 49
Need SSIS project 2 30
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

730 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