Post reply

Stored procedure divide

  • May 6, 2008 at 3:30 am

    #186105

    Hello,

    In my script I want to dive (Oooh nooo !)

    here's my (very simple) code:

    ALTER PROC TESTTELLEN AS

    BEGIn

    declare @ber float

    set @ber = 6/5

    print '--> '+convert(char(255),@ber)

    END

    When I use calc the result of 6/5 is 1.2

    When i run the stored procedure the output is 1 the stred removes evertything after the , ..

    I'm looking at my code for an hour, and i can't find the problem, can anyone give me the answer to this f***ing problem?

  • May 6, 2008 at 3:51 am

    #811080

    set @ber = 6/5 is doing an integer divide, hence throwing away the fraction. Try the following

    set @ber = 6.0/5.0

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 6, 2008 at 3:55 am

    #811082

    OMFG that's very stupid ... but now:

    IN my real project I need to divide 2 integers to calculate the % of log space used so:

    (@used / @max-2)*100 ==> because the division < 0 I need to use a float, .. and when I execute that query he always says its 0, but that's then because I divide 2 ints :/

    Anyone how to solve this?

  • May 6, 2008 at 3:58 am

    #811085

    Cast one or both to float/numeric before doing the divide

    (CAST(@used AS FLOAT)/ CAST(@max AS FLOAT))*100

    Use float is you're not worried about floating point inaccuracies, otherwise use a numeric with the appropriate precision and scale.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 6, 2008 at 5:10 am

    #811106

    :w00t: nice it works ! 🙂

  • May 6, 2008 at 5:30 am

    #811112

    Pleasure.

    It's simply a data type and conversion issue. Any expression that just involves integers will return an integer. If the expression inlcludes multiple types, then the result will be of the type with the higher precedence.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply