Running updates in SP as batches

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Running updates in SP as batches

Author

Topic

alanmac
Starting Member

26 Posts

Posted - 2012-06-08 : 05:33:40

Hi,

I have an SP which will run a series of updates against the same table, but at the moment they are using incorrect values:

UPDATE
ud1
SET
AnnualLeaveOnContract = ud1.AnnualLeaveOnContractNextYear
from UserDetails ud1 join EmployeeHours eh on ud1.UserDetailsId = eh.UserDetailsID

UPDATE
ud
SET
AnnualLeaveOnContractNextYear = (SELECT dbo.CalculateAnnualLeaveOnContract(
eh.HoursPerWeek,
ud.PRSYears,
ud.PRSMonths,
DATEADD(yyyy, -1,ud.DateJoinedNHS))),

AnnualLeaveCarriedOver = ud.AnnualLeaveEntitlement - ud.AnnualLeaveTaken
from UserDetails id join EmployeeHours eh on ud.UserDetailsId = eh.UserDetailsID

When I run these two commands together, the AnnualLeaveOnContract and AnnualLeaveOnContractNextYear have the same value, whereas if I run them seperately I get the desired effect (the AnnualLeaveOnContractNextYear field calculates on a prorata basis for those whose length of service is within a certain range).

At the moment I believe the AnnualLeaveOnContract field is being populated with the NEW AnnualLeaveOnContractNextYear value, which is why both are the same. I tried adding the GO statement between calls like this:

UPDATE
ud1
SET
AnnualLeaveOnContract = ud1.AnnualLeaveOnContractNextYear
from UserDetails ud1 join EmployeeHours eh on ud1.UserDetailsId = eh.UserDetailsID

GO

UPDATE
ud
SET
AnnualLeaveOnContractNextYear = (SELECT dbo.CalculateAnnualLeaveOnContract(
eh.HoursPerWeek,
ud.PRSYears,
ud.PRSMonths,
DATEADD(yyyy, -1,ud.DateJoinedNHS))),

AnnualLeaveCarriedOver = ud.AnnualLeaveEntitlement - ud.AnnualLeaveTaken
from UserDetails id join EmployeeHours eh on ud.UserDetailsId = eh.UserDetailsID

GO

However, this throughs up an error (Incorrect syntax near UserDetailsID ).

Can anyone tell me how to run the first query, resolve those changes to the database, and then run the new query?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-08 : 05:56:03

You can't have a "go" in an SP.
In the last query the UserDetails table is aliased as id instead of ud.
Bit surprised about the error message though - would have expected brackets to be wrong.

Running the sttaements in the same batch or separately shouldn't make any difference as there dodesn't seem to be a connection betwen the queries unless there is something odd in the function.
If yoou are executing in a single statement then the values used shoould be those before any updates - but it wouldn't surprise me if the function causes the value to be calculated before being passed to the other update.
To get round this you could try using a CTE to get the value before the update then use that for the query.

By together do you mean as a sigle query? In which case

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-06-08 : 06:38:04

UPDATE
ud
SET
AnnualLeaveOnContractNextYear = (SELECT dbo.CalculateAnnualLeaveOnContract(
eh.HoursPerWeek,
ud.PRSYears,
ud.PRSMonths,
DATEADD(yyyy, -1,ud.DateJoinedNHS))),

AnnualLeaveCarriedOver = ud.AnnualLeaveEntitlement - ud.AnnualLeaveTaken
from UserDetails [b]ud[b] join EmployeeHours eh on ud.UserDetailsId = eh.UserDetailsID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources