Returns zero not a value

Question

Issue: PassedPercent returns 0 but there should be a number (I can confirm in all cases @Started and @Passed have numerical values). The same formula worked correctly on the .aspx page but I'm converted it to an SQL query.

Code:

USE DATABASE

DECLARE @i INT = 0 
DECLARE @ModuleID  bigint
DECLARE @Started   bigint
DECLARE @Completed  bigint
DECLARE @Passed  bigint

DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT [ModuleID] FROM dbo.[TblModules] 
--ORDER BY [ModuleID]
OPEN merge_cursor

FETCH NEXT FROM merge_cursor INTO @ModuleID

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @Started =  (Select Count(ID) FROM [TblResults] WHERE [ModuleID] = @ModuleID) 
    SET @Completed = (Select Count(ID) FROM [TblResults] WHERE [ModuleID] = @ModuleID and ModuleDatecomplete <> '')
    SET @Passed = (Select Count(ID) FROM [TblResults] WHERE [ModuleID] = @ModuleID and Pass = 'Yes')

    UPDATE [TblModules]  SET [Started] = @Started,[Completed] = @Completed,[Passed] = @Passed,[PassedPercent] = ((@Passed / @Started) * 100)
    WHERE [ModuleID] = @ModuleID     

    FETCH NEXT FROM merge_cursor INTO @ModuleID 
END

CLOSE merge_cursor
DEALLOCATE merge_cursor

@jarlh but only on sufficiently large values of 0. :p – xQbert Nov 30 '16 at 14:03 — xQbert, Nov 30 '16 at 14:03

score 3 · Accepted Answer · answered Nov 30 '16 at 13:42

3

An int / int will return an int (in this case zero)

Try

 ,[PassedPercent] = (@Passed *100.0) / @Started

answered Nov 30 '16 at 13:42

John Cappelletti

79,615
7
44
66

2

@indofraiser The formula in your question will always return 0 when `@Passed<@Started` (assuming both are positive) since "If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated." (see https://msdn.microsoft.com/en-us/library/ms175009.aspx), so John is right! – piet.t Nov 30 '16 at 13:49
Cheers, I was just deleting my other comment and about to ask the answer you put!) – indofraiser Nov 30 '16 at 13:49
@indofraiser Give it a shot, you may be surprised. For example try: Select 25/100 and then try Select 25.0/100 the first will return a zero while the second will return 0.25 – John Cappelletti Nov 30 '16 at 13:50
1

So both values are integers. The system must return 1 and only one data type for the result. Since it's two values are integers it uses int, thus you get 0. If you change one to a decimal (simply by adding `.0`), the engine chooses the value with the greatest opportunity for precision and will return a decimal. Alternatively you could cast each value to decimal before the division occurs. This situation is often referred to as "Integer Math" in SQL server and you always need to be aware of it. I don't believe this happens in Oracle, MySQL, or DB2. – xQbert Nov 30 '16 at 14:01

Returns zero not a value

1 Answers1