CODEWITHSUNDEEP
sqlsql-servert-sql
Edvardoss
Edvardoss

Reputation: 393

Got integer result when printed return numeric

Not quite understood why I got @res = 1 when print return 0.57? I need to return the numeric results in my UDF function.

DECLARE @text1 VARCHAR(255) = 'some text'
DECLARE @text2 VARCHAR(255) = 'same another text'
DECLARE @res AS NUMERIC
DECLARE @i INT = 0
DECLARE @exist_counter INT = 0

WHILE @i < (LEN(@text1) - 2)
BEGIN
    SET @i = @i + 1

    IF CHARINDEX(SUBSTRING(@text1, @i, 3), @text2) > 0
    BEGIN
        SET @exist_counter = @exist_counter + 1
            --print @exist_counter
            --print SUBSTRING(@text1,@i,3)
    END
END

PRINT @i
PRINT @exist_counter
PRINT cast(@exist_counter AS NUMERIC) / cast(nullif(@i, 0) AS NUMERIC)

SET @res = cast(@exist_counter AS NUMERIC) / cast(nullif(@i, 0) AS NUMERIC)

PRINT @res

Upvotes: 0

Views: 43

Answers (1)

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14218

From this post

Numeric data types that have fixed precision and scale

So you should change scale like.

declare @res as numeric(18, 2)

Why?

As @HABO's comment, "The default precision is 18." and "The default scale is 0" where scale is "The number of decimal digits that are stored to the right of the decimal point."

Upvotes: 2

Related Questions