February 21, 2008 at 1:02 pm
Just when I think I understand it.
The tbl_Clients_ClientBasicInformation has both the ClientNumber and the SQL assigned Identity key Client_PK_ID.
The records in tbl_Clients_ClientWageRates are identified only with the Client_PK_ID. Given this, I do not think that a JOIN is needed between the two tables. Once I get the Client_PK_ID, I should be able to pull the info from the tbl_Clients_ClientWageRates.
-----------------
get the Client_PK_ID value from tbl_Clients_ClientBasicInformation based on value of ClientNumber
SELECT Client_PK_ID
FROM
tbl_Clients_ClientBasicInformation
WHERE ---this is line 40
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber
BEGIN TRY
---using the Client_PK_ID value from above to determine if there
--is a record in tbl_Clients_ClientWageRates with the matching
---Client_PK_ID.
IF EXISTS(
SELECT *
--Client_PK_ID
FROM
tbl_Clients_ClientWageRates
WHERE
Client_PK_ID =
tbl_Clients_ClientWageRates.Client_PK_ID)
---if there is a matching Client_PK_ID, then do the updates
BEGIN
BEGIN TRANSACTION
UPDATE tbl_Clients_ClientWageRates
SET
AverageWage = coalesce(@AverageWage, AverageWage),
FringeWage = coalesce(@FringeWage, FringeWage),
SpecialWage = coalesce(@SpecialWage, SpecialWage),
GuaranteedWage = coalesce(@GuaranteedWage, GuaranteedWage),
FutureUseField1 = coalesce(@FutureUseField1, FutureUseField1),
WageChangeDate = coalesce(@WageChangeDate, WageChangeDate)
WHERE
following line does not create an error, but every record
in tbl_Clients_ClientWageRates is updated. should not be.
tbl_Clients_ClientWageRates.Client_PK_ID =
Client_PK_ID
following gives "The multi-part identifier
"tbl_Clients_ClientBasicInformation.Client_PK_ID"
could not be bound." error. says line 53.
tbl_Clients_ClientWageRates.Client_PK_ID =
tbl_Clients_ClientBasicInformation.Client_PK_ID
following gives "The multi-part identifier
"tbl_Clients_ClientBasicInformation.ClientNumber"
could not be bound." error. says line 53.
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber ---this is line 81
END
ELSE
---I can't even get the part below to light up on this sproc.
BEGIN
IF NOT EXISTS(
SELECT *
FROM
tbl_ClientsClientWageRates
WHERE
tbl_Clients_ClientWageRates.Client_PK_ID =
Client_PK_ID)
BEGIN
set @Message = 'Incorrect Client Number'
return 1
END
END
COMMIT ---IF WE GOT THIS FAR WITH NO ERRORS, COMMIT DATA
END TRY
As always, thanks.
February 21, 2008 at 1:17 pm
SELECT Client_PK_ID
FROM tbl_Clients_ClientBasicInformation
WHERE ---this is line 40
tbl_Clients_ClientBasicInformation.ClientNumber = @ClientNumber
Are you setting this to a variable?
IF EXISTS
(SELECT *--Client_PK_ID
FROMtbl_Clients_ClientWageRates
WHERE Client_PK_ID = tbl_Clients_ClientWageRates.Client_PK_ID)
I dont get this... These will always match... Shouldnt this be your variable @Client_PK_ID?
tbl_Clients_ClientWageRates.Client_PK_ID = Client_PK_ID
Again, wont these always match? Again I believe this should be @Client_PK_ID
February 21, 2008 at 1:18 pm
Your code comments state this:
>>get the Client_PK_ID value from tbl_Clients_ClientBasicInformation based on value of ClientNumber
But you're not "getting" the Client_PK_ID. You're selecting the column, but not assigning it to anything.
If you want to "get" the value, for use later in your query, you need to declare a variable to hold the value, and then use the variable later:
DECLARE @Client_PK_ID As integer
SELECT @Client_PK_ID = Client_PK_ID
FROM ....
[edit] IOW, what Adam said
February 21, 2008 at 1:55 pm
Adam/PW,
I had declared the @Client_PK_ID, I just wasn't putting it to use.
You guys are awesome! I'm going to print out this and other posts for reference. Hopefully it will begin to sink in.
Thanks again!
Bill
February 21, 2008 at 4:23 pm
Is there a unique index on tbl_Clients_ClientBasicInformation.ClientNumber ? If not, you are leaving yourself with the possibility that your initial SELECT returning multiple rows and your UPDATE not working as expected.
If there is an index on that column, a simple JOIN in your UPDATE statement solves everything. You'll know if the row existed if it gets updated :). Are you worried about the overhead of the UPDATE statement? If so, why? If not, why not do this in one statement and get rid of the procedural mumbo-jumbo?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply