Assigning value to variable. Another attempt.

Question

Post reply

Assigning value to variable. Another attempt.

willyboy

Ten Centuries

Points: 1121
More actions
March 4, 2008 at 3:47 pm

#184722

It would not surprise me if someone wanted to reach through the internet and throttle me.
Using the code from a previous post for updating timesheets, I have tried to do the same with JobSteps. The following is returning the @JobNumber_PK_ID and the @StepNumber_PK_ID values as it should. It is just not updating the JobSteps table. I have also tried to cut down on the amount of code being used. I still feel that there must be a better/shorter way.
There have been many different combinations of code in my attempt.
SET @JobNumber_PK_ID =
(select JobNumber_PK_ID
FROM
tbl_Jobs_JobNumber
JOIN
tbl_Customers_CustomerBasicInformation
ON
tbl_Jobs_JobNumber.Customer_PK_ID =
tbl_Customers_CustomerBasicInformation.Customer_PK_ID
WHERE
tbl_Customers_CustomerBasicInformation.CustomerNumber = @CustomerNumber
AND
tbl_Jobs_JobNumber.JobNumber = @JobNumber
AND
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber)
---------------------------------------
--SET @StepNumber_PK_ID =(
SELECT @StepNumber_PK_ID = StepNumber_PK_ID
FROM
tbl_Jobs_JobSteps
WHERE
/*
@StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
AND
*/
@JobNumber_PK_ID = tbl_Jobs_JobSteps.JobNumber_PK_ID
AND
@StepNumber = tbl_Jobs_JobSteps.StepNumber
AND
@LocationNumber = tbl_Jobs_JobSteps.LocationNumber
BEGIN TRY
IF EXISTS(
SELECT *
FROM
tbl_Jobs_JobSteps
WHERE
@StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
/* AND
@JobNumber_PK_ID = tbl_Jobs_JobSteps.JobNumber_PK_ID
AND
@StepNumber = tbl_Jobs_JobSteps.StepNumber
AND
@LocationNumber = tbl_Jobs_JobSteps.LocationNumber
*/
)
BEGIN
BEGIN TRANSACTION
UPDATE tbl_Jobs_JobSteps
SET
@StepDescription = upper(coalesce(@StepDescription, StepDescription)),
@PrevailingWage = coalesce(@PrevailingWage, PrevailingWage),
@StepStandard = coalesce(@StepStandard, StepStandard),
@GuaranteedStepWage = coalesce(@GuaranteedStepWage, GuaranteedStepWage),
@MinimumWageCap = coalesce(@MinimumWageCap, MinimumWageCap)
WHERE
@StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
/*AND
@JobNumber_PK_ID = tbl_Jobs_JobSteps.JobNumber_PK_ID
AND
@StepNumber = tbl_Jobs_JobSteps.StepNumber
AND
@LocationNumber = tbl_Jobs_JobSteps.LocationNumber
*/
END
ELSE --…..
/* ---this is from the TimeSheetEntry sproc. It works.
BEGIN
BEGIN TRANSACTION
UPDATE tbl_TimeSheetEntry
SET HoursWorked = coalesce(@HoursWorked, HoursWorked),
Units = coalesce(@Units, Units)
WHERE
@TimeSheet_PK_ID =
tbl_TimeSheetEntry.TimeSheet_PK_ID
END
ELSE
*/
----back to the JobSteps update.
BEGIN
IF NOT EXISTS(
SELECT *
FROM
tbl_Jobs_JobSteps
WHERE
@StepNumber_PK_ID = tbl_Jobs_JobSteps.StepNumber_PK_ID
)
BEGIN
SET @Message = 'Desired Step Record Does Not Exist'
RETURN 1
END
END
COMMIT
END TRY
It seems that I always get hung up on the tiniest of details when the answer isn’t that difficult. As always, thanks.
Bill

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

willyboy (3/4/2008)
It would not surprise me if someone wanted to reach through the internet and throttle me.

Depends... do ya like porkchops? 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

willyboy (3/4/2008)
It is just not updating the JobSteps table.

How do you know that? What if it's updating the table with the same stuff that you have in it (NULLS?) because the variables just aren't being populated? Have you tried printing the content of the variables to see what they actually contain?;)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ksullivan SSCrazy Points: 2346 More actions · Answer 3

One thing you can do to shorten the code is skip the search for the id and go straight to the update. Then, check if any record is updated by evaluating @@rowcount. If that is zero, then go to "no record matched your conditions."

GSquared SSC Guru Points: 260824 More actions · Answer 4

I don't think I've ever used this syntax:

@StepDescription = upper(coalesce(@StepDescription, StepDescription)),

@PrevailingWage = coalesce(@PrevailingWage, PrevailingWage),

@StepStandard = coalesce(@StepStandard, StepStandard),

@GuaranteedStepWage = coalesce(@GuaranteedStepWage, GuaranteedStepWage),

@MinimumWageCap = coalesce(@MinimumWageCap, MinimumWageCap)

I would use:

StepDescription = upper(coalesce(@StepDescription, StepDescription)),

PrevailingWage = coalesce(@PrevailingWage, PrevailingWage),

StepStandard = coalesce(@StepStandard, StepStandard),

GuaranteedStepWage = coalesce(@GuaranteedStepWage, GuaranteedStepWage),

MinimumWageCap = coalesce(@MinimumWageCap, MinimumWageCap)

The way it's written in the proc, it will update the value of the variable, but won't update any columns in the table. Since that seems to be the problem, try it the 2nd way above. See if that works.

Edit: The above are from the update statement for the table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

O jeez... I totally missed that... yes, that would be the problem. Nice job, Gus.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

willyboy Ten Centuries Points: 1121 More actions · Answer 6

G Squared,

Thanks. I dropped the @'s in the SET and it worked. It sure is great to have another set of knowlegable eyes to look at stuff.

Jeff,

I don't understand the pork chop reference, but, yes I do. A nice pork loin with a bit of seasoning and some applesauce along with.... Whoops. Sorry. I should be in the cooking forum.

Thanks for the help.

Bill:P

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

No, no... don't drop all the @'s... they should be on the right side of the = sign just like GSquared showed...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

willyboy Ten Centuries Points: 1121 More actions · Answer 8

Jeff,

I only dropped the left @'s. The ones inside the coalesce are still there.

Thanks for the follow up.

Bill

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Whew. Ok... just wanted to make sure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)