| Author |
 Topic  |
|
|
alanmac
Starting Member
United Kingdom
24 Posts |
 Posted - 06/08/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/08/2012 : 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
Malaysia
789 Posts |
Posted - 06/08/2012 : 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 |
|
|
| |
Topic |
|
|
|
Running updates in SP as batches
