如何在 SQL Server 中返回动态十进制数据类型?

Question

我试图向下面的函数添加两个参数并将结果返回为 DECIMAL(@p1，@p2(，但我收到语法错误 - 我该怎么做？

/*
---------------------------------------------------------------------------------
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

更新- 在我发现这需要与SQL Server 2008兼容后，我添加了这个婴儿：

/*
---------------------------------------------------------------------------------
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

幻数是： (38， 10， 29( (19， 6， 14( (19， 4， 16(

Answer 1

您不能在 SQL Server 中执行此操作(以合理的方式(。 问题出在哪里？ 不能将精度和长度作为参数传递给decimal。

"显而易见"的解决方案是使用动态SQL。 唉，这行不通，因为函数不能调用动态 SQL(嗯，有一种方法，但它涉及的开销太大，你不妨嘲笑变通方法(。

另一个解决方案是一个非常有趣的case表达式：

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

只有大约 38 * 38/2 = 722 个条件(精度不能小于小数(。

我不确定你为什么真正关心数值的精度和小数位数。 为什么不直接使用str()或format()并转换为具有所需表示形式的字符串？

编辑：

在编写了所有这些之后，我意识到该函数必须返回单个类型。 您无法参数化返回值的小数位数和精度，因此 SQL Server 根本不支持您想要执行的操作。