Division in a Stored Procedure

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 2000 Forums

SQL Server Development (2000)

Division in a Stored Procedure

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-04-28 : 10:53:56

I have a stored procedure and I want to divide one temporary variable by another variable and to assign the result to another variable. It is returning 0 instead of what it should be.
I have highlighted the line in red.

Anyone any ideas,
macca

declare @TdHH int
declare @TdPerm Decimal
declare @TdTotal int

SELECT @TempPerm = TP_TOTAL, @TempHH = THH_TOTAL FROM Table1
WHERE Tperm = 'another'

print @tempperm
print @temphh

SET @TdTotal = @TempPerm + @TempHH

print @tdtotal

SELECT @TdPerm = (@TempPerm /@TdTotal) * 100

PRINT @TdPerm
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 11:02:17

This is known as INTEGER MATH.

Use

SELECT @TdPerm = 100.0 * @TempPerm / @TdTotal

instead

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 11:29:23

quote:
Originally posted by Peso

This is known as INTEGER MATH.

Use

SELECT @TdPerm = 100.0 * @TempPerm / @TdTotal

instead

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

and make sure you dont have 0 values coming in @TdTotal else it will cause divison by 0 error. If you are unceratin you can avoid it by modifying like this

SELECT @TdPerm = 100.0 * ISNULL(@TempPerm / NULLIF(@TdTotal,0),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources