Stored Procedure not doing math

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 not doing math

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-17 : 14:26:13

What happens is I need to check for zeros and do math if one does not exist or it its exists just make a zero, but it only works when both counts ar the same and never does the math if both are different

ALTER Proc [dbo].[StrengthReport]

AS

declare @UnitId int,@MobId int,@UIC varchar(6),@UName varchar(60),@Req int,@Asgn int,@PAsgn int,@OverStruct int,@OSAsgn int, @POSAsgn int,@TAsgn int,@PTAsgn int, @State int,@NGB int,@AC int

Declare Strength CURSOR FOR Select ud.intUnitMobId,ud.intMobilizationId,ud.strUic,o.orgstrUName
From tblUnitDeployData as ud LEFT OUTER JOIN cms.dbo.tblOrganization as o on o.orgstrUPC = ud.strUIC Order by intUnitMobId

OPEN Strength

FETCH NEXT FROM Strength INTO @UnitiD,@MobId,@UIC,@UName

while @@FETCH_STATUS = 0

begin

--Find all the report Data and assign to variables
--Count the required strength
Select @Req = Sum(intReqstr) from tblUnitPosition where intUnitMobId = @UnitId AND Substring(strpara, 1, 1) NOT IN ('9')

--Get the Asgned strength of a Unit
Select @Asgn = Sum(intAsgnStr) From tblUnitPosition Where intUnitMobID = @UnitId AND Substring(strpara, 1, 1) NOT IN ('9')

--Get the Percent ASGN
IF @Asgn > 0
SET @PAsgn = @Asgn / @Req
ELSE IF @Asgn = 0
SET @PAsgn = 0

I can put all the begin and ends in but it still nevers does the math, but I can do the same thing in other parts of code such as

Select @OverStruct = ISNULL(Count(intReqStr), 0) from tblUnitPosition where intUnitMobID = @UnitId AND Substring(strpara, 1, 1) = 9

--Get the O/S ASGN
Select @OSAsgn = ISNULL(SUM(intAsgnStr), 0) from tblUnitPosition where intUnitMobID = @UnitId AND Substring(strpara, 1, 1) = 9

--Get the Percentage of O/S Assigned
If @OSAsgn > 0
SET @POSAsgn = (@OSAsgn / @OverStruct) * 100
ELSE IF @OSAsgn = 0
Set @POSAsgn = 0

This above works just fine in another part of the same SP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 14:44:29

1. Why are you using a cursor?
2. If you can post some sample data and expected ouput (so we can actually execute a query), then we'd probably be able to help more. Here is a link that should help:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 14:54:36

Hey, can you do us all a favor, and just say, in business terms, what you are trying to do?

Leave out all of the cursors, sets, if, ect

Just say

I want to calculate the avg value of quarts of milk by the number of cows

or something like that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-11-18 : 08:14:37

The problem has been solved, my problem was in trying to do division calculations I ws using a intteger intstead of a float and it could not handle the calculations.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-18 : 08:22:26

quote:
Originally posted by kdeutsch

The problem has been solved, my problem was in trying to do division calculations I ws using a intteger intstead of a float and it could not handle the calculations.

I wanted to post this when I read the question
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources