February 20, 2008 at 12:39 pm
In the following, there is no error thrown if a record does not
exists in either the BasicInformation table or the WageRates
table for the desired client when using this sproc to update
a client's wage rate(s).
No entry is made, but no error is thrown.
Manually entering info into the WageRates table does throw a FK Ref Integ error.
---following can be taken out and checked from UI app
IF @ClientNumber is null or len(@ClientNumber) <> 4
begin
set @Message = 'Valid Client Number Required'
set @continue = 0
end
------------------------------
/*
---tried the following
SELECT
Client_PK_ID
FROM
tbl_Clients_ClientBasicInformation
WHERE
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber
*/
----------------------
BEGIN TRY
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
/* ---in conjunction with SELECT above
tbl_Clients_ClientWageRates.Client_PK_ID =
Client_PK_ID
*/
---Current attempt. Not working. Also tried a JOIN with the WageRates table.
Client_PK_ID =
(SELECT
Client_PK_ID
FROM
tbl_Clients_ClientBasicInformation
WHERE
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber)
---------------------------
COMMIT
END TRY
BEGIN CATCH
---THERE IS AN ERROR
IF @@TRANCOUNT > 0
ROLLBACK
---ERROR INFO
DECLARE @ErrMsg varchar(1000),
@ErrSeverity int
---other error codes??
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
---KEEP FOR ASP
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
Thanks again,
Bill
February 20, 2008 at 1:03 pm
---THERE IS AN ERRORIF @@TRANCOUNT > 0ROLLBACK---ERROR INFODECLARE @ErrMsg varchar(1000),@ErrSeverity int---other error codes??SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()---KEEP FOR ASPRAISERROR(@ErrMsg, @ErrSeverity, 1)
You dont need an if for the rollback. You want to rollback regardless of trancount. You know for a fact that an error occured because you are in the catch block.
Additionally, I would rollback after raising all the errors.
BEGIN CATCH
---ERROR INFO
DECLARE @ErrMsg varchar(1000),
@ErrSeverity int
---other error codes??
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
---KEEP FOR ASP
RAISERROR(@ErrMsg, @ErrSeverity, 1)
ROLLBACK TRANSACTION
END CATCH
February 20, 2008 at 1:31 pm
Also - you don't get an error, because your WHERE clause is throwing out the update.
If you want the CATCH to fire - take the WHERE clause out.
Better yet - prevent the error and just have the CATCH for all of the things you didn't think of.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 1:59 pm
If I take out the WHERE, then how will I be able to select the desired client to make a change in their WageRate record?
BEGIN TRY
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 ---<<Take this one out?
Client_PK_ID =
(SELECT
Client_PK_ID
FROM
tbl_Clients_ClientBasicInformation
WHERE
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber)
COMMIT
END TRY
February 20, 2008 at 2:16 pm
You cant remove the where because you need it for the update. What he is saying is you can do is either put the variable in the update like: (This will cause the error to fire if there is RI issues)
BEGIN TRY
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 ---<<Take this one out?
Client_PK_ID = @ClientNumber
COMMIT
END TRY
Or you can do a check
BEGIN TRY
IF EXISTS(
SELECT 1
FROM tbl_Clients_ClientBasicInformation a
WHERE a.ClientNumber = @ClientNumber)
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 Client_PK_ID = @ClientNumber)
END
ELSE
BEGIN
--RAISE ERROR OR RETURN VALUE TO INDICATE FAILURE
--E.G. RETURN 0
END
COMMIT TRANSACTION
END TRY
February 20, 2008 at 2:25 pm
What Adam said....:)
Time to change the bag on the caffeine infusion..... :blink:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 2:45 pm
LOL. Yeah my pack is getting kinda low too.
February 20, 2008 at 3:27 pm
I have to be careful about this time of the day. Things are still eloquent in the peabrain - but they kind of come out as "just ditch the WHERE clause"....oops...:hehe:
that being said -
Willyboy - I see where you're going, but you probably don't want to be raising errors. It's just not the greatest way to do this. What you want to look at is returning a status code BACK to the UI telling it that the insert failed. It's a lot less carnage on the Server side than continuous application errors.
Keep the CATCH for stuff that's off the wall (the stuff that doesn't occur to you). Meaning - use Adam's 2nd example and return non zero return codes and check them out on the UI end. ROLLBACK's are resource-intensive, so code to only see them as infrequently as you can.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 20, 2008 at 3:36 pm
Thanks guys.
I'm gonna take a break and eat some dinner. Maybe my sub-conscious will work on it for a while.
I'll get there.
Thanks,
Bill
February 21, 2008 at 10:21 am
The way the code is currently written below, if the desired/selected ClientNumber does NOT exist in the BasicInformation table, then the “SET @Message = 'Does Not Exist' happens, as it should.
But if ClientNumber is ANY valid ClientNumber, then all records in the table are updated, not just the one desired/selected.
The problem, I believe is in the IF EXIST below and the IF NOT EXIST that follows the UPDATE. They are not selecting the desired/specific Client_PK_ID (or anything for that matter using *) that is needed for the UPDATE.
To see the values, I am using PRINT at the end of the sproc.
It is showing the ClientNumber but not the Client_PK_ID, telling me that it is not selecting the Client_PK_ID as it should.
BEGIN TRY
IF EXISTS(
SELECT *
---Client_PK_ID
FROM
tbl_Clients_ClientBasicInformation
WHERE
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber)
BEGIN
BEGIN TRANSACTION
UPDATE tbl_Clients_ClientBasicInformation
SET
SocialSecurity = coalesce(@SocialSecurity, SocialSecurity),
LastName = upper(coalesce(@LastName, LastName)),
FirstName = upper(coalesce(@FirstName, FirstName)),
----other parameters here
BasicInfoNotes = upper(coalesce(@BasicInfoNotes, BasicInfoNotes))
WHERE
tbl_Clients_ClientBasicInformation.Client_PK_ID =
Client_PK_ID
END
ELSE
BEGIN
IF NOT EXISTS(
SELECT *
---Client_PK_ID
FROM
tbl_Clients_ClientBasicInformation
WHERE
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber)
BEGIN
set @Message = 'Does Not Exist'
return 1
END
END
COMMIT ---IF WE GOT THIS FAR WITH NO ERRORS,
---COMMIT DATA
END TRY
The above problem is similar to a previous issue I posted on 2-16-2008 “SELECT with IF EXISTS not working”. With help, I was able to get it to work as follows:
IF @PayFormulaCode = 'TS' AND (@Units = 0 OR @Units IS NULL)
IF EXISTS(
SELECT *
FROM tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
BEGIN
SELECT
@TimeStudy_PK_ID =
tbl_Clients_ClientTimeStudy.TimeStudy_PK_ID,
@TimeStudyPercent =
tbl_Clients_ClientTimeStudy.TimeStudyPercent,
@PrevailingWage =
tbl_Jobs_JobSteps.PrevailingWage
FROM
tbl_Clients_ClientTimeStudy
JOIN
tbl_Jobs_JobSteps
ON
tbl_Jobs_JobSteps.StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
AND
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
END
ELSE
IF @PayFormulaCode = 'TS' AND (@Units = 0 OR @Units IS NULL)
IF NOT EXISTS(
SELECT *
FROM
tbl_Clients_ClientTimeStudy
WHERE
@Client_PK_ID =
tbl_Clients_ClientTimeStudy.Client_PK_ID
AND
@StepNumber_PK_ID =
tbl_Clients_ClientTimeStudy.StepNumber_PK_ID
)
BEGIN
SET @Message = 'No Time Study Record.'
--rollback
return 1
END
The only differences that I see are:
1)the tables used,
2)the “IF @PayFormulaCode = 'TS' AND (@Units = 0 OR @Units IS NULL)” and
3) the “AND @StepNumber_PK_ID = tbl_Clients_ClientTimeStudy.StepNumber_PK_ID” statements.
Any help is always appreciated.
Bill
February 21, 2008 at 10:33 am
Just changed
tbl_Clients_ClientBasicInformation.Client_PK_ID =
Client_PK_ID
to
tbl_Clients_ClientBasicInformation.ClientNumber =
@ClientNumber
and Wha-La! It (appears) to work!
Thanks,
Bill
February 21, 2008 at 10:42 am
hopping in a bit late 🙂
In stead of the if exists ...
you might as well check the update's @@rowcount and if that is not what you expected it to be, raise an error or jump to your error routine
BEGIN TRANSACTION
UPDATE tbl_Clients_ClientWageRates
SET....
if @@Rowcount = 0
begin
-- do you realy need the rollback ?
-- raiserror(....)
fill the error variables
end
COMMIT
END TRY
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply