Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure

Author

Topic

pgrooms
Starting Member

4 Posts

Posted - 2010-03-02 : 16:12:27

I need to update records one at a time. Below is a copy of my Stored Procedure so far but it updates ALL records with the same information. Can someone please help????This is on a deadline to start using ASAP.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Philip Grooms
-- Create date: 3/3/2010
-- Description: Stored Procedure to Update FSC Records
-- =============================================
ALTER PROCEDURE [dbo].[FSCUpdate]

-- Add the parameters for the stored procedure here
(
@bdate DATETIME,
@edate DATETIME
)
AS
declare @AmountSurcharge decimal(4,2)
declare @CurrentFSC decimal(4,2)
declare @Miles decimal(5,0)
declare @Tons decimal(5,2)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @CurrentFSC=CurrentFSC, @Miles=Miles, @Tons=Tons from Hauling where DateEntered >= @bdate and DateEntered <= @edate

set @AmountSurcharge = (@Miles*@CurrentFSC)/@Tons
Update Hauling set AmountSurcharge=@AmountSurcharge where DateEntered >= @bdate and DateEntered <= @edate
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 16:16:54

Try this and delete the select and set to fill the variables
Update Hauling
set AmountSurcharge=(Miles * CurrentFSC)/Tons
where DateEntered >= @bdate and DateEntered <= @edate

No, you're never too old to Yak'n'Roll if you're too young to die.

pgrooms
Starting Member

4 Posts

Posted - 2010-03-02 : 16:27:15

Excellent!!!!!I don't know how I missed that. Now I can move forward with this. I still have other columns to update based on calculations and still need to join to another table to retrieve data in one column for CurrentFSC.

Thanks for the fast reply!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 16:32:25

you're welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

pgrooms
Starting Member

4 Posts

Posted - 2010-03-02 : 16:35:39

One more thing.....I need to perform an update based on the value of a column such as:
If ContTrl=1 then add this to this
else
If ContTrl=0 then Multply this to this.

Any ideas? This is inside the same stored procedure.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 16:39:30

[code]
update table
set column1 = case
when ConTrl=1 then colx + coly
when ConTrl=0 then colz * cola
else column1
end
where ...

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

pgrooms
Starting Member

4 Posts

Posted - 2010-03-02 : 16:59:39

Perfect!!!!!! I have it now. You now your stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources