今天我们来学习SQL Server中自定义函数的用法，重点解决一个常见问题：如何让函数返回多个值。
一、为什么要用自定义函数？
真实需求场景
有一个业务需求：处理带小数的数值，规则如下：
如果在30个字段中都要重复写这段逻辑，代码会非常冗余。这时就需要用函数来封装这个逻辑。
二、自定义函数基础
函数基本语法
CREATE FUNCTION 函数名( @参数1 数据类型, @参数2 数据类型)RETURNS 返回类型ASBEGIN RETURN 返回值END
三、返回多个值的三种方案
方案一：返回表变量（Table）
IF OBJECT_ID('GetInt_decimal') IS NOT NULL DROP FUNCTION GetInt_decimalGO
CREATE FUNCTION GetInt_decimal( @p DECIMAL(18,8))RETURNS @table TABLE ( IntPart INT, DecimalPart DECIMAL(18,8) )ASBEGIN DECLARE @Int_Num INT DECLARE @decimal_Num DECIMAL(18,8)
IF @p - CAST(@p AS INT) <> 0 BEGIN SET @Int_Num = 1 + CAST(@p AS INT) SET @decimal_Num = @p - CAST(@p AS INT) END ELSE BEGIN SET @Int_Num = @p SET @decimal_Num = 0 END
INSERT INTO @table (IntPart, DecimalPart) VALUES (@Int_Num, @decimal_Num)
RETURNENDGO
SELECT * FROM dbo.GetInt_decimal(14.2)
SELECT * FROM dbo.GetInt_decimal(14.0)
优点：
结构清晰，返回多个字段
可以JOIN其他表使用
可读性好
缺点：
方案二：返回拼接字符串
IF OBJECT_ID('GetInt_decimal') IS NOT NULL DROP FUNCTION GetInt_decimalGO
CREATE FUNCTION GetInt_decimal( @p DECIMAL(18,8))RETURNS VARCHAR(50)ASBEGIN DECLARE @Int_Num INT DECLARE @decimal_Num DECIMAL(18,8) DECLARE @Result VARCHAR(50)
IF @p - CAST(@p AS INT) <> 0 BEGIN SET @Int_Num = 1 + CAST(@p AS INT) SET @decimal_Num = @p - CAST(@p AS INT) END ELSE BEGIN SET @Int_Num = @p SET @decimal_Num = 0 END
SET @Result = CAST(@Int_Num AS VARCHAR) + '|' + CAST(@decimal_Num AS VARCHAR)
RETURN @ResultENDGO
SELECT dbo.GetInt_decimal(14.2) AS Result
SELECT CAST(LEFT(Result, CHARINDEX('|', Result) - 1) AS INT) AS IntPart, CAST(RIGHT(Result, LEN(Result) - CHARINDEX('|', Result)) AS DECIMAL(18,8)) AS DecimalPartFROM ( SELECT dbo.GetInt_decimal(14.2) AS Result) t
优点：
缺点：
方案三：使用OUTPUT参数（存储过程方式）
注意：函数不支持OUTPUT参数，只能通过表值函数实现类似效果。
CREATE PROCEDURE usp_GetInt_decimal @p DECIMAL(18,8), @IntPart INT OUTPUT, @DecimalPart DECIMAL(18,8) OUTPUTASBEGIN IF @p - CAST(@p AS INT) <> 0 BEGIN SET @IntPart = 1 + CAST(@p AS INT) SET @DecimalPart = @p - CAST(@p AS INT) END ELSE BEGIN SET @IntPart = @p SET @DecimalPart = 0 ENDENDGO
DECLARE @i INT, @d DECIMAL(18,8)EXEC usp_GetInt_decimal 14.2, @i OUTPUT, @d OUTPUTSELECT @i AS IntPart, @d AS DecimalPart
四、完整实战：批量处理30个字段
场景描述
有一张销售表，包含30个产品的销售数量（带小数），需要计算每个产品的：
方案一：使用表值函数批量处理
IF OBJECT_ID('fn_ProcessDecimal') IS NOT NULL DROP FUNCTION fn_ProcessDecimalGO
CREATE FUNCTION fn_ProcessDecimal( @value DECIMAL(18,8))RETURNS @result TABLE ( WholeNumber INT, Remainder DECIMAL(18,8) )ASBEGIN DECLARE @intPart INT DECLARE @decPart DECIMAL(18,8)
IF @value - CAST(@value AS INT) <> 0 BEGIN SET @intPart = 1 + CAST(@value AS INT) SET @decPart = @value - CAST(@value AS INT) END ELSE BEGIN SET @intPart = @value SET @decPart = 0 END
INSERT INTO @result VALUES (@intPart, @decPart) RETURNENDGO
CREATE TABLE SalesOrder ( OrderID INT PRIMARY KEY, Product1 DECIMAL(18,8), Product2 DECIMAL(18,8), Product3 DECIMAL(18,8), CreateDate DATETIME DEFAULT GETDATE())
INSERT INTO SalesOrder (OrderID, Product1, Product2, Product3)VALUES (1, 14.2, 8.5, 3.0), (2, 23.7, 15.0, 7.3), (3, 5.0, 9.8, 12.4)
SELECT so.OrderID, p1.WholeNumber AS Product1_Whole, p1.Remainder AS Product1_Remainder, p2.WholeNumber AS Product2_Whole, p2.Remainder AS Product2_Remainder, p3.WholeNumber AS Product3_Whole, p3.Remainder AS Product3_RemainderFROM SalesOrder soCROSS APPLY dbo.fn_ProcessDecimal(so.Product1) p1CROSS APPLY dbo.fn_ProcessDecimal(so.Product2) p2CROSS APPLY dbo.fn_ProcessDecimal(so.Product3) p3
方案二：使用内联表值函数（性能更好）
IF OBJECT_ID('fn_ProcessDecimal_Inline') IS NOT NULL DROP FUNCTION fn_ProcessDecimal_InlineGO
CREATE FUNCTION fn_ProcessDecimal_Inline( @value DECIMAL(18,8))RETURNS TABLEASRETURN( SELECT CASE WHEN @value - CAST(@value AS INT) <> 0 THEN 1 + CAST(@value AS INT) ELSE CAST(@value AS INT) END AS WholeNumber, CASE WHEN @value - CAST(@value AS INT) <> 0 THEN @value - CAST(@value AS INT) ELSE 0 END AS Remainder)
SELECT OrderID, p1.WholeNumber AS Product1_Whole, p1.Remainder AS Product1_RemainderFROM SalesOrder soCROSS APPLY dbo.fn_ProcessDecimal_Inline(so.Product1) p1
内联表值函数优点：
性能最好（无函数体开销）
可以加WHERE条件
支持索引推送
五、函数类型对比
性能对比示例
CREATE FUNCTION fn_Scalar(@value DECIMAL(18,8))RETURNS INTASBEGIN RETURN CASE WHEN @value - CAST(@value AS INT) <> 0 THEN 1 + CAST(@value AS INT) ELSE CAST(@value AS INT) ENDEND
CREATE FUNCTION fn_Inline(@value DECIMAL(18,8))RETURNS TABLEASRETURN( SELECT CASE WHEN @value - CAST(@value AS INT) <> 0 THEN 1 + CAST(@value AS INT) ELSE CAST(@value AS INT) END AS Result)
CREATE FUNCTION fn_MultiStatement(@value DECIMAL(18,8))RETURNS @result TABLE (Result INT)ASBEGIN DECLARE @r INT IF @value - CAST(@value AS INT) <> 0 SET @r = 1 + CAST(@value AS INT) ELSE SET @r = CAST(@value AS INT)
INSERT INTO @result VALUES (@r) RETURNEND
六、最佳实践总结
✅ 推荐做法
优先使用内联表值函数：性能最好
函数命名规范：使用fn_前缀，明确函数用途
添加注释：说明函数功能、参数、返回值
考虑NULL值处理：使用ISNULL或COALESCE
使用SCHEMABINDING：绑定架构，提升性能
CREATE FUNCTION dbo.fn_Example(@value INT)RETURNS TABLEWITH SCHEMABINDING ASRETURN( SELECT @value AS Result)
❌ 避免做法
在函数中使用非确定性函数（除非必要）
返回大量数据（改用视图或存储过程）
嵌套调用过多函数
在WHERE条件中使用函数处理索引列
总结
核心收获：
阅读原文：原文链接
该文章在 2026/4/15 18:29:39 编辑过
400 186 1886
