Avatar of Bruce Gust
Bruce GustFlag for United States of America asked on

How would I write this stored procedure?

Here's my current stored procedure:

USE [BIProd]
/****** Object:  StoredProcedure [dbo].[cer_CapxUpdateTemp]    Script Date: 3/7/2014 10:07:11 AM ******/

ALTER PROCEDURE [dbo].[cer_CapxUpdateTemp] --Parms for stored procedure
            @cercapxid as int,
            @allocatedamount as int
            declare @key as int
            declare @ErrorMessage as varchar(300)
            declare @ErrorNo as int
            set @ErrorMessage = ''
            set @ErrorNo = 0

                  Update cer_CapX set allocatedamount = @allocatedamount
                  where cercapxid = @cercapxid
                  set @ErrorNo = @@Error
                  if @ErrorNo <> 0
                        set @key = 0
                        set @ErrorMessage = 'Failed to update the record (' + cast(@ErrorNo as varchar) + '}'

The crux of the proc being

Update cer_CapX set allocatedamount = @allocatedamount
where cercapxid = @cercapxid

Here's my challenge:

I've got look out for allocated amounts that are great than what's actually been budgeted. I have access to that info which is located in the "totalcost" column of the very table I'm updating.

Here's where I'm a pig on rollerskates. How do I check to see if my user's allocated amount is greater than the total cost, and if so, how do I update that amount to the total cost value, rather than what they've inputted AND still update the other rows where the data is acceptable (meaning the amount allocated is either equal to or less than the total cost value)?

I'm thinking the logic would look something like this:

update cer_Capx set allocatedamount =
//rather than @allocatedamount, I would need to do a select statement / comparison
select allocatedamount as original_allocatedamount, totalcost as original_totalcost from cer_Capx where cercapxid=@cercapxid

if (@allocatedamount>original_totalcost)
@allocatedamount = original_allocatedamount;
@allocatedamount = @allocatedamount;
where cercapxid=@cercapxid...

But how do I code that correctly?

            select @key as ID, @ErrorMessage as ErrorMessage
Microsoft SQL Server

Avatar of undefined
Last Comment
Bruce Gust

8/22/2022 - Mon
Jerry Miller

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bruce Gust

Yo, JMiller!

This looks great!

The original_totalcost variable. I was planning on establishing that via a select statement based on the capxid. What I came up with was:

select totalcost as original_totalcost, allocatedamount from cer_Capx where cercapxid=@cercapxid
                  if (original_totalcost>allocatedamount)
                  Update cer_CapX set allocatedamount = original_totalcost where cercapxid=@cercapxid
                  Update cer_Capx set allocatedamount = @allocatedamount where cercapxid = @cercapxid

It doesn't work. I'm getting errors that read "Invalid column name, 'original_totalcost."

I'm sitting here pondering what I could do differently and then I see your post and I'm thinking "Awesome!" But how are you defining "original_totalcost?" Is there a select statement in your syntax that I'm blowing past? Should I try to pass that into my function and have it in place like that?

What do you think?
Bruce Gust

I got it!

if @allocatedamount > (select totalcost from cer_Capx where cercapxid=@cercapxid)
                  Update cer_CapX set allocatedamount = (select totalcost from cer_Capx where cercapxid=@cercapxid) where cercapxid=@cercapxid
                  Update cer_Capx set allocatedamount = @allocatedamount where cercapxid = @cercapxid

Thanks for your input!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.