I need to be able to loop through my table to update rows based on changes

Question

Hi there,

I am using SQL 2000, and I realize that my naming coventions are obselete.  I apologize in advance. 

I have a .NET web app that has work referrals with original required work hours and new required work hours.  If an end-user changes the record by inputting new required hours on the referral then I want to update the old hours to match the new hours, and then I want to log it in another table.  I need to loop through all work referrals to check for this change.  I appreciate your help on this.  This code works if I pass in one referralID as a parameter:

DECLARE @intOldHoursType1 INT
DECLARE @intOldHoursType2 INT
DECLARE @intOldHoursType3 INT 
DECLARE @intNewHoursType1 INT
DECLARE @intNewHoursType2 INT
DECLARE @intNewHoursType3 INT
DECLARE @datRequiredHoursChangeDate SMALLDATETIME
DECLARE @intRequiredHoursChangedBy INT --the user that changed the required hours
DECLARE @datNewRequiredHoursEffectiveDate SMALLDATETIME
DECLARE @intNewRecordID INT  -- status type  ID of new record
DECLARE @intReferralCount INT -- count of work contract
SELECT @intReferralCount = COUNT(ReferralID) 
FROM tblClientReferral
WHERE ReferralID = @pintReferralID
SELECT 	
		@intOldHoursType1 = HoursType1Hours,
		@intOldHoursType2 = HoursType2Hours,
@intOldHoursType3 = HoursType3Hours,
		@intNewHoursType1 = NewHoursType1Hours,
		@intNewHoursType2 = NewHoursType2Hours,
		@intNewHoursType3 = NewHoursType3Hours,
		@datRequiredHoursChangeDate = RequiredHoursChangeDate,
		@intRequiredHoursChangedBy = RequiredHoursChangedBy,
		@datNewRequiredHoursEffectiveDate = NewRequiredHoursEffectiveDate
		
	FROM dbo.tblClientReferral
	WHERE ReferralID = @pintReferralID
	
	--These variables ensure that there was a change or else no log is entered
DECLARE @strBaseLogMessage VARCHAR(2000)  --To be inserted into the log table Description field
DECLARE @strLogMessage VARCHAR(2000)  --To be inserted into the log table Description field
SET @strBaseLogMessage = 'User has updated the required hours.'
SET @strLogMessage = @strBaseLogMessage + '  '
--Compare new values to old and write an entry to the log message for each that is different
--ANSI_NULLS are OFF
IF @intOldHoursType1 <>@intNewHoursType1
	SET @strLogMessage = @strLogMessage + 'HoursType1 changed from ' + ISNULL(CAST (@intOldHoursType1 AS VARCHAR(20)), 'NULL') + ' to ' + ISNULL(CAST (@intNewHoursType1 AS VARCHAR(20)), 'NULL') + '.  '
IF @intOldHoursType2<>@intNewHoursType2
	SET @strLogMessage = @strLogMessage + 'HoursType2 hours changed from ' + ISNULL(CAST (@intOldHoursType2 AS VARCHAR(20)), 'NULL') + ' to ' + ISNULL(CAST (@intNewHoursType2 AS VARCHAR(20)), 'NULL') + '.  '
IF @intOldHoursType3 <> @intNewHoursType3
	SET @strLogMessage = @strLogMessage + HoursType3 hours changed from ' + ISNULL(CAST(@intOldHoursType3 AS VARCHAR(20)), 'NULL') + ' to ' + ISNULL(CAST (@intNewHoursType3 AS VARCHAR(20)), 'NULL') + '.  '
--Do Not enter record if Referral ID does not exist in table.
IF (@intReferralCount < 1)
    BEGIN
	RAISERROR('Referral ID does not exist', 16, 1)
	SET @intError = @@ERROR
    END
ELSE IF ((@intOldHoursType1 <>@intNewHoursType1) OR (@intOldHoursType2<>@intNewHoursType2) OR (@intOldHoursType3 <> @intNewHoursType3))
     BEGIN
	UPDATE dbo.tblClientReferral
	SET	HoursType1Hours = @intNewHoursType1,
		HoursType2Hours = @intNewHoursType2,
		HoursType3Hours = @intNewHoursType3
	WHERE 	ReferralID = @pintReferralID
	--Trap any errors
	SET @intError = @@ERROR
	--If an error occurred during the delete, then delete the log entry
	IF (@intError = 0)
	    BEGIN
		--Log the INSERT in the appropriate table
		INSERT INTO dbo.logClientReferralChangeRequiredHours
			(ReferralID, ChangeDate, EffectiveDate, ChangedBy, Description )
		VALUES (@pintReferralID, 
			
			@datRequiredHoursChangeDate,
			@datNewRequiredHoursEffectiveDate,
			@intRequiredHoursChangedBy,
			@strLogMessage)
	    END
    END

Answer 1

I am using SQL 2000, and I realize that my naming coventions are obselete.  I apologize in advance. 

Let's see if this apology stops Celko from posting his standard rant on some ISO standard about name that no one else has heard about! I would not count on it.

I will have to admit that I get a little more worried when you say ANSI_NULLS are OFF.

Anyway, I don't have the time for a full analysis right now, but it seems to me that it should be possible to do these changes set-based which is usually preferrable to a loop - unless you anticipate that errors could occur, and in such case you want to update the referrals that you can update.

In the latter case, why not put the code you have in a stored procedure and then call that for each @pintReferralID. Of course you need a away to identify which rows to update, that is not exactly clear to me how you would do.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

@Erland

That is just too funny!!!

---

Ryan Shuell

Answer 3

Let's see if this apology stops Celko from posting his standard rant on some ISO standard about name that no one else has heard about! I would not count on it.

I haven't been to the forum for a while, and this is the first thing I see when I log in. You made my day. 

---

Abdallah El-Chal, PMP

Answer 4

Erland,

I'm not exactly sure what you mean?

Answer 5

It would have helped if you had been more specific in what you did not understand. Anyway,

Let's see if this apology stops Celko from posting his standard rant on some ISO standard about name that no one else has heard about! I would not count on it.

This is an internal joke about Joe Celko who figures in this forum and whose manners leaves a lot to be desired. If you see his posts, please ignore him.

I will have to admit that I get a little more worried when you say ANSI_NULLS are OFF.

Yes, I am worried. This is a legacy setting, and not all features in SQL Server works when this setting is OFF. You are on SQL 2000, but there are more and more features requiring ANSI_NULLS to be on coming in later versions.

Anyway, I don't have the time for a full analysis right now, but it seems to me that it should be possible to do these changes set-based which is usually preferrable to a loop - unless you anticipate that errors could occur, and in such case you want to update the referrals that you can update.

Unfortuntely, I'm again in the situation that I don't have the time to dig into the details. But this is the key here: why do you want to loop? Because you think that there are no other alternatives, or because you want don't want errors for one record mean that no other records are updated?

If you could answer that question, I hope that I will have the time for a closer look next time.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

First of all, I'm well aware of Celko and that's why I apologized to begin with.  I'm sure my naming convention offends others, as well.  I took over coding for a guy that left and just kept the old convention.

I'm not worried about errors.  The loop is what I thought would be needed for the solution.  Right now this procedure is only good for one ReferralID. First, I need to get all of the ReferralID's with new hours and then (I believe) I need to loop through each referral and  update, and then log the changes. I am not sure why ANSI_NULLS is off, I will be sure to change that.

Answer 7

Here, I believe something like this will work for you.  It removes the loop, does the log insert (kind of) and will update only the changed rows.  I haven't tested it (if it doesn't work, provide some sample data and I can work on it a bit more), and the Log insert will need a little work to get the proper message (I started writing out this big case statement, but I'm not sure that's the best way; I will try to work on that), but hopefully this is at least a step in the right direction:

Declare @tvHours Table (
	ReferralID						int
	,OldHoursType1					int
	,OldHoursType2					int
	,OldHoursType3					int
	,NewHoursType1					int
	,NewHoursType2					int
	,NewHoursType3					int
	,RequiredHoursChangeDate		datetime
	,RequiredHoursChangedBy			int
	,NewRequiredHoursEffectiveDate	datetime
	)

Insert	@tvHours
Select	ReferralID
		,OldHoursType1
		,OldHoursType2
		,OldHoursType3
		,NewHoursType1
		,NewHoursType2
		,NewHoursType3
		,RequiredHoursChangeDate
		,RequiredHoursChangedBy
		,NewRequiredHoursEffectiveDate
From	dbo.tblClientReferral
Where	OldHoursType1 <> NewHoursTypes1
	Or	OldHoursType2 <> NewHoursTypes2
	Or	OldHoursType3 <> NewHoursTypes3
	
Insert	dbo.logClientReferralChangeRequiredHours
		(ReferralID
		,ChangeDate
		,EffectiveDate
		,ChangedBy
		,Description)
Select	ReferralID
		,RequiredHoursChangeDate
		,NewRequiredHoursEffectiveDate
		,RequiredHoursChangedBy
		,{LogMessage} --The only thing I can thing of at this very moment is a very large nexted case structure, but I think maybe using STUFF somehow will do what you want here.
From	@tvHours

Update	x
Set		x.OldHoursType1 = Case
							When th.OldHoursType1 <> th.NewHoursType1 Then th.NewHoursType1
							Else x.OldHoursType1
						  End
		,x.OldHoursType2 = Case
							When th.OldHoursType2 <> th.NewHoursType2 Then th.NewHoursType2
							Else x.OldHoursType2
						  End
		,x.OldHoursType3 = Case
							When th.OldHoursType3 <> th.NewHoursType3 Then th.NewHoursType3
							Else x.OldHoursType3
						  End
From	dbo.tblClientReferral x
join	@tvHours th
	on	x.ReferralID = th.ReferralID

Answer 8

Had a thought.  Maybe this will work for your Log Insert:

Insert	dbo.logClientReferralChangeRequiredHours
		(ReferralID
		,ChangeDate
		,EffectiveDate
		,ChangedBy
		,Description)
Select	ReferralID
		,RequiredHoursChangeDate
		,NewRequiredHoursEffectiveDate
		,RequiredHoursChangedBy
		,'User has updated the required hours. '
				+ IsNull(
				(	Select	Case
								When th2.OldHoursType1 Is Not Null And th2.NewHoursType1 Is Not Null Then 'HoursType1 changed from ' + ISNULL(CAST (th2.OldHoursType1 AS VARCHAR(20)), 'NULL') + ' to ' + ISNULL(CAST (th2.NewHoursType1 AS VARCHAR(20)), 'NULL') + '.  '
								Else null
					From	@tvHours th2
					Where	th2.ReferralID = th.ReferralID)
				+ (	Select	Case
								When th2.OldHoursType2 Is Not Null And th2.NewHoursType2 Is Not Null Then 'HoursType1 changed from ' + ISNULL(CAST (th2.OldHoursType2 AS VARCHAR(20)), 'NULL') + ' to ' + ISNULL(CAST (th2.NewHoursType2 AS VARCHAR(20)), 'NULL') + '.  '
								Else null
					From	@tvHours th2
					Where	th2.ReferralID = th.ReferralID)
				+ (	Select	Case
								When th2.OldHoursType3 Is Not Null And th2.NewHoursType3 Is Not Null Then 'HoursType1 changed from ' + ISNULL(CAST (th2.OldHoursType3 AS VARCHAR(20)), 'NULL') + ' to ' + ISNULL(CAST (th2.NewHoursType3 AS VARCHAR(20)), 'NULL') + '.  '
								Else null
					From	@tvHours th2
					Where	th2.ReferralID = th.ReferralID)
				, '')
					
From	@tvHours th

Answer 9

dgjohnson,

Thanks for your help.  I'm all set now.  Have a great one!