SQLServer自定义函数完全指南：从单值返回到多值返回

今天我们来学习SQL Server中自定义函数的用法，重点解决一个常见问题：如何让函数返回多个值。

一、为什么要用自定义函数？

真实需求场景

有一个业务需求：处理带小数的数值，规则如下：

14.2 → 返回 15 和 0.8（整数部分+1，小数部分不变）
14.0 → 返回 14 和 0（整数部分不变，小数部分为0）

如果在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)      -- 整数部分+1        SET @decimal_Num = @p - CAST(@p AS INT)  -- 保留小数部分    END    ELSE    BEGIN        SET @Int_Num = @p                        -- 整数不变        SET @decimal_Num = 0                     -- 小数为0    END
    -- 插入结果到表变量    INSERT INTO @table (IntPart, DecimalPart)    VALUES (@Int_Num, @decimal_Num)
    RETURNENDGO
-- 使用示例SELECT * FROM dbo.GetInt_decimal(14.2)-- 结果：IntPart=15, DecimalPart=0.8
SELECT * FROM dbo.GetInt_decimal(14.0)-- 结果：IntPart=14, DecimalPart=0

优点：

结构清晰，返回多个字段
可以JOIN其他表使用
可读性好

缺点：

调用稍复杂（需要FROM）
不能直接用于表达式

方案二：返回拼接字符串

-- 创建函数：返回拼接字符串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-- 结果：15|0.8
-- 需要拆分使用时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参数，只能通过表值函数实现类似效果。

-- 如果需要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-- 结果：15, 0.8

四、完整实战：批量处理30个字段

场景描述

有一张销售表，包含30个产品的销售数量（带小数），需要计算每个产品的：

实际发货数量（整数部分+1）
剩余数量（小数部分）

方案一：使用表值函数批量处理

-- 1. 创建函数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
-- 2. 创建测试表CREATE TABLE SalesOrder (    OrderID INT PRIMARY KEY,    Product1 DECIMAL(18,8),    Product2 DECIMAL(18,8),    Product3 DECIMAL(18,8),    -- ... 以此类推到Product30    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)
-- 3. 使用CROSS APPLY批量处理SELECT     so.OrderID,    -- 处理Product1    p1.WholeNumber AS Product1_Whole,    p1.Remainder AS Product1_Remainder,    -- 处理Product2    p2.WholeNumber AS Product2_Whole,    p2.Remainder AS Product2_Remainder,    -- 处理Product3    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条件
支持索引推送

五、函数类型对比

类型	语法	返回类型	性能	适用场景
标量函数	RETURNS 数据类型	单值	较差	简单计算
内联表值函数	RETURNS TABLE	表（无BEGIN...END）	最好	数据查询
多语句表值函数	RETURNS @table TABLE	表（有BEGIN...END）	一般	复杂逻辑

性能对比示例

-- 创建三种函数对比-- 1. 标量函数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
-- 2. 内联表值函数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)
-- 3. 多语句表值函数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
-- 测试性能（10万次调用）-- 标量函数：约3000ms-- 内联表值：约500ms-- 多语句表值：约2000ms

六、最佳实践总结

✅ 推荐做法

优先使用内联表值函数：性能最好
函数命名规范：使用fn_前缀，明确函数用途
添加注释：说明函数功能、参数、返回值
考虑NULL值处理：使用ISNULL或COALESCE
使用SCHEMABINDING：绑定架构，提升性能

-- 带架构绑定的函数CREATE FUNCTION dbo.fn_Example(@value INT)RETURNS TABLEWITH SCHEMABINDING  -- 防止被意外删除ASRETURN(    SELECT @value AS Result)

❌ 避免做法

在函数中使用非确定性函数（除非必要）
返回大量数据（改用视图或存储过程）
嵌套调用过多函数
在WHERE条件中使用函数处理索引列

总结

需求	推荐方案
返回单值	内联表值函数
返回多字段	内联表值函数
返回多行	多语句表值函数
复杂计算逻辑	多语句表值函数
需要OUTPUT参数	改用存储过程

核心收获：

函数封装业务逻辑，减少代码冗余
返回多个值有三种方案：表变量、拼接字符串、存储过程
内联表值函数性能最好，优先使用

阅读原文：原文链接