CODEWITHSUNDEEP
sqlsql-server
Yovav
Yovav

Reputation: 2777

How to return a dynamic decimal data type in SQL Server?

I was trying to add two parameters to the function below and return the result as DECIMAL(@p1, @p2) but I'm getting a syntax error for it - how can I do that?

/*
---------------------------------------------------------------------------------
SELECT [dbo].fn_ConvertTextToDecimal_19_4('', 0) -- NULL
SELECT [dbo].fn_ConvertTextToDecimal_19_4('', 1) -- 0.0000
SELECT [dbo].fn_ConvertTextToDecimal_19_4('100', 1) -- 100.0000
SELECT [dbo].fn_ConvertTextToDecimal_19_4('1234567890123456.1234', 0) -- NULL
---------------------------------------------------------------------------------
*/
ALTER FUNCTION [dbo].[fn_ConvertTextToDecimal_19_4]
(
    @Decimal_Number VARCHAR(50),
    @Fail_As_Zero BIT
)
RETURNS DECIMAL(19, 4)
AS
BEGIN
    RETURN 
    (
        CASE WHEN 
                TRY_CAST(@Decimal_Number AS DECIMAL(19, 4)) IS NULL
            THEN
                CASE WHEN (@Fail_As_Zero = 1)
                    THEN 0
                    ELSE NULL
                END
            ELSE 
                TRY_CAST(@Decimal_Number AS DECIMAL(19, 4))
        END
    )
END

UPDATE - after I found that this needs to be compatible with SQL Server 2008 I added this baby:

/*
---------------------------------------------------------------------------------
PURPOSE     : Convert a string into decimal(19,4)
    (this is a workaround for SQL server 2008 where TRY_CAST cannot be used)
    based on https://stackoverflow.com/questions/11089125/varchar-to-decimal
    otherwise it can be casted as follows:
    ISNULL(TRY_CAST([column_name] AS DECIMAL(19, 4)), 0) AS [Test_1]
    ISNULL(TRY_CAST([column_name] AS DECIMAL(19, 4)), NULL) AS [Test_2]
NOTE        : for different precision, change the constants in the code (19, 4, 16)
USAGE       :
SELECT [dbo].fn_ConvertTextToDecimal_19_4('', 0) -- NULL
SELECT [dbo].fn_ConvertTextToDecimal_19_4('', 1) -- 0.0000
SELECT [dbo].fn_ConvertTextToDecimal_19_4('100', 1) -- 100.0000
SELECT [dbo].fn_ConvertTextToDecimal_19_4('-123456789012345.1234', 0) -- -123456789012345.1234
---------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fn_ConvertTextToDecimal_19_4]
(
    @Decimal_Number VARCHAR(50),
    @Fail_As_Zero BIT
)
RETURNS DECIMAL(19, 4)
AS
BEGIN
    RETURN 
    (
        CASE WHEN ISNUMERIC(@Decimal_Number) = 1
                AND CHARINDEX('.', @Decimal_Number) = 0
                AND LEN(REPLACE(REPLACE(@Decimal_Number, '-', ''), '+', '')) < 16
                THEN CONVERT(DECIMAL(19, 4), @Decimal_Number)
            WHEN ISNUMERIC(@Decimal_Number) = 1
                AND (CHARINDEX('.', @Decimal_Number) !=0
                    AND CHARINDEX('.', REPLACE(REPLACE(@Decimal_Number, '-', ''), '+', '')) <= 16)
                THEN CONVERT(DECIMAL(19, 4), 
                    CASE WHEN LEN(@Decimal_Number) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Decimal_Number, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')) <= 19 
                        THEN @Decimal_Number 
                        ELSE SUBSTRING(@Decimal_Number, 1, 19 + LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Decimal_Number, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''))) 
                    END
                )
            ELSE
                CASE WHEN (@Fail_As_Zero = 1)
                    THEN 0
                    ELSE NULL
                END
            END
    )
END
GO

Magic numbers are: (38, 10, 29) (19, 6, 14) (19, 4, 16)

Upvotes: 0

Views: 1144

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You cannot do this (in a reasonable way) in SQL Server. The problem? You cannot pass precision and length to decimal as parameters.

The "obvious" solution is to use dynamic SQL. Alas, that doesn't work, because functions cannot invoke dynamic SQL (well there is a way, but it involves so much overhead that you might as well laugh at the work-around).

Another solution would be a really fun case expression:

(case when @p = 1 and @s = 1 then try_cast(@input as decimal(1, 1))
      . . .

There would only be about 38 * 38 / 2 = 722 conditions (precision cannot be smaller than scale).

I'm not sure why you actually care about the precision and scale of a numeric value. Why not just use str() or format() and convert to a string with the representation you want?

EDIT:

After writing all of this, I realize that the function has to return a single type. You can't parameterize the scale and precision on the return value, so SQL Server simply doesn't support what you want to do.

Upvotes: 3

Related Questions