Loop won't end. - 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)

Loop won't end.

Author

Topic

avarndel
Starting Member

5 Posts

Posted - 2008-04-18 : 11:13:33

I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.

AV

Set NoCount on
Declare @Amt as bigint
Declare @Cont as bigint
Declare @Mark as Bigint
Declare @Total as bigint
Declare @BudgetNodeID as Bigint
Declare @GTotal as bigint
Set @BudgetNodeID ='120'
Set @Amt = 0
set @Cont = 0
set @Mark = 0
set @GTotal = 0

While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin
select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')

select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Mark = @Cont * (xBudgetNodeCosts.Markup/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
-- compute the sell

select @Total = @Amt + @Cont + @Mark

-- add to grand total
Select @GTotal = Sum(@Total+ @GTotal)

select @GTOtal
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 11:39:16

BudgetNodeID variable never changes.

E 12°55'05.25"
N 56°04'39.16"

avarndel
Starting Member

5 Posts

Posted - 2008-04-18 : 11:48:08

what is the best way to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-18 : 14:02:13

I have no idea of what your business rules are nor look like.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 14:03:33

Post what your requirement is along with structures of table involved,some sample data and your exepected o/p out of them.

avarndel
Starting Member

5 Posts

Posted - 2008-04-18 : 14:14:56

Table name is xBudgetNodeCosts

BgetNodeCostID
BudgetNodeID
Qty
CostRate
Contengency
Markup
ProposalChangeID

Table name is xBudget
BudgetID
BudgetNodeID
IsActive

Table name xProposalChange
ProposalChangeID
Status

I pass the BudgetNodeID into the stored Proc and for each row that has the BudgetNodeID I want to calculate the following Qty * Cost where xbudget is approved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources