对表值函数的传参查询方案-表库云知库网

BOM查询系统最终完整方案存档

系统架构图

前端程序
↓
usp_GetBOM (统一入口)
├─ 参数解析(A0/A1)
│
├─ 正向查询(usp_GetBOM_Forward)
│ └─ 调用 ZJBOM2_djwl() 函数
│
└─ 反向查询(usp_GetBOM_Reverse)
└─ 调用 ZJBOM2_zjwl() 函数
│
└─ 基于 ZJBOM2() 基础函数

核心函数定义

1.1 基础BOM查询函数（ZJBOM2）

USE [ZKZN]
GO
/** Object: UserDefinedFunction [dbo].[ZJBOM2] Script Date: 2025/12/12 18:19:45 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 基础BOM查询函数（无参数，返回全系统BOM）
ALTER FUNCTION [dbo].[ZJBOM2]()
RETURNS TABLE
AS
RETURN
(
WITH BOM_CTE AS (
-- 锚点成员：顶级物料
SELECT
F111882 AS TopLevelMaterial,
F111882 AS Material,
CAST('1' AS VARCHAR(MAX)) AS LevelCode,
CAST('001' AS VARCHAR(MAX)) AS SortCode,
F111904 AS ParentMaterial,
CAST(F111891 AS DECIMAL(18, 8)) AS BOMUsage,
CAST(F111891 AS DECIMAL(18, 8)) AS TotalUsage,
1 AS Level
FROM dbo.tabDIYTable12746
WHERE ISNULL(F111904, '') = ''
AND ISNULL(F111882, '') <> ''
AND ISNULL(F111891, 0) > 0

    UNION ALL

    -- 递归成员：子物料
    SELECT
        c.TopLevelMaterial,
        b.F111882,
        -- 优化：避免字符串操作性能问题
        CASE WHEN c.Level < 15 
             THEN CAST(c.LevelCode + '.' + 
                    CAST(ROW_NUMBER() OVER (
                        PARTITION BY b.F111904 
                        ORDER BY b.F111882
                    ) AS VARCHAR(10)) AS VARCHAR(MAX))
             ELSE c.LevelCode + '.x'
        END AS LevelCode,
        -- 优化：减少字符串拼接
        CASE WHEN c.Level < 15
             THEN CAST(c.SortCode + '.' + 
                    RIGHT('000' + CAST(
                        ROW_NUMBER() OVER (
                            PARTITION BY b.F111904 
                            ORDER BY b.F111882
                        ) AS VARCHAR(3)
                    ), 3) AS VARCHAR(MAX))
             ELSE c.SortCode + '.999'
        END AS SortCode,
        b.F111904,
        CAST(b.F111891 AS DECIMAL(18, 8)),
        CAST(CASE WHEN c.Level < 15 
                  THEN ROUND(c.TotalUsage * b.F111891, 8)
                  ELSE 0
             END AS DECIMAL(18, 8)),
        c.Level + 1
    FROM dbo.tabDIYTable12746 b
    -- 使用INNER JOIN，强制使用索引
    INNER JOIN BOM_CTE c 
        ON b.F111904 = c.Material
        AND c.Level < 15  -- 提前终止递归
    WHERE ISNULL(b.F111882, '') <> '' 
      AND ISNULL(b.F111891, 0) > 0
)

SELECT 
    TopLevelMaterial,
    LevelCode,
    SortCode,
    ParentMaterial,
    Material AS LeafMaterial,
    BOMUsage,
    TotalUsage,
    Level
FROM BOM_CTE
WHERE ISNULL(Material, '') <> ''
  AND Level <= 15

)
GO

1.2 正向查询函数（ZJBOM2_djwl）

USE [ZKZN]
GO
/** Object: UserDefinedFunction [dbo].[ZJBOM2_djwl] Script Date: 2025/12/12 18:20:00 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 正向查询函数：返回指定物料的完整BOM
CREATE OR ALTER FUNCTION [dbo].[ZJBOM2_djwl]
(
@MaterialCode NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
-- 直接从ZJBOM2()函数过滤指定物料
SELECT
TopLevelMaterial,
LevelCode,
SortCode,
ParentMaterial,
LeafMaterial,
BOMUsage,
TotalUsage,
Level
FROM [dbo].[ZJBOM2]()
WHERE TopLevelMaterial = @MaterialCode
)
GO

1.3 反向查询函数（ZJBOM2_zjwl）

USE [ZKZN]
GO
/** Object: UserDefinedFunction [dbo].[ZJBOM2_zjwl] Script Date: 2025/12/12 18:32:29 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 反向查询函数：返回所有包含指定子物料的完整BOM
ALTER FUNCTION [dbo].[ZJBOM2_zjwl]
(
@LeafMaterialCode NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
WITH TopLevelMaterials AS (
-- 获取所有包含该子物料的顶级物料
SELECT DISTINCT bom.TopLevelMaterial
FROM dbo.ZJBOM2() bom
WHERE bom.LeafMaterial = @LeafMaterialCode
AND bom.TopLevelMaterial <> bom.LeafMaterial
)
SELECT
b.TopLevelMaterial,
b.LevelCode,
b.SortCode,
b.ParentMaterial,
b.LeafMaterial,
b.BOMUsage,
b.TotalUsage,
b.Level
FROM dbo.ZJBOM2() b
INNER JOIN TopLevelMaterials tm ON b.TopLevelMaterial = tm.TopLevelMaterial
)
GO

结果表结构

USE [ZKZN]
GO

-- 创建BOM_Result表
IF OBJECT_ID('dbo.BOM_Result', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[BOM_Result] (
ID BIGINT IDENTITY(1,1) PRIMARY KEY,
UserName NVARCHAR(50) NOT NULL,
MaterialCode NVARCHAR(100) NOT NULL, -- 查询的物料编码
QueryID NVARCHAR(50) NOT NULL, -- 查询批次号
TopLevelMaterial NVARCHAR(100) NOT NULL, -- 顶级物料
LevelCode NVARCHAR(1000) NOT NULL, -- 层级编码
SortCode NVARCHAR(1000) NOT NULL, -- 排序编码
ParentMaterial NVARCHAR(100) NULL, -- 上级物料
LeafMaterial NVARCHAR(100) NOT NULL, -- 子物料
BOMUsage DECIMAL(18,8) NULL, -- 单层用量
TotalUsage DECIMAL(18,8) NULL, -- 总用量
Level INT NOT NULL, -- 层级深度
CreatedTime DATETIME DEFAULT GETDATE()
);
PRINT '已创建表: BOM_Result';
END
GO

存储过程

3.1 正向查询存储过程

USE [ZKZN]
GO
/** Object: StoredProcedure [dbo].[usp_GetBOM_Forward] Script Date: 2025/12/12 16:00:00 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 正向查询存储过程：返回指定物料的完整BOM
CREATE OR ALTER PROCEDURE [dbo].[usp_GetBOM_Forward]
@FullParam NVARCHAR(150) -- 格式："A0物料编码$用户名"
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @MaterialCode NVARCHAR(100);
DECLARE @UserName NVARCHAR(50);
DECLARE @QueryID NVARCHAR(50);
DECLARE @DollarIndex INT;

BEGIN TRY
    -- 1. 参数解析
    SET @FullParam = LTRIM(RTRIM(@FullParam));

    IF @FullParam = '' OR LEFT(@FullParam, 1) <> 'A'
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 去掉"A0"前缀
    DECLARE @ParamWithoutPrefix NVARCHAR(150) = SUBSTRING(@FullParam, 3, LEN(@FullParam) - 2);
    SET @DollarIndex = CHARINDEX('$', @ParamWithoutPrefix);

    IF @DollarIndex <= 0
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    SET @MaterialCode = LEFT(@ParamWithoutPrefix, @DollarIndex - 1);
    SET @UserName = SUBSTRING(@ParamWithoutPrefix, @DollarIndex + 1, LEN(@ParamWithoutPrefix));
    SET @MaterialCode = LTRIM(RTRIM(@MaterialCode));
    SET @UserName = LTRIM(RTRIM(@UserName));

    IF LEN(@MaterialCode) = 0 OR LEN(@UserName) = 0
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 检查物料是否存在
    IF NOT EXISTS (SELECT 1 FROM dbo.tabDIYTable12746 WHERE F111882 = @MaterialCode)
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 生成查询ID
    SET @QueryID = LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 8);

    -- 删除用户历史数据
    DELETE FROM dbo.BOM_Result WHERE UserName = @UserName;

    -- 查询BOM并插入结果
    INSERT INTO dbo.BOM_Result (
        UserName,
        MaterialCode,
        QueryID,
        TopLevelMaterial,
        LevelCode,
        SortCode,
        ParentMaterial,
        LeafMaterial,
        BOMUsage,
        TotalUsage,
        Level
    )
    SELECT 
        @UserName,
        @MaterialCode,
        @QueryID,
        TopLevelMaterial,
        LevelCode,
        SortCode,
        ParentMaterial,
        LeafMaterial,
        BOMUsage,
        TotalUsage,
        Level
    FROM dbo.ZJBOM2_djwl(@MaterialCode)
    ORDER BY SortCode
    OPTION (MAXRECURSION 15, RECOMPILE);

    -- 返回查询结果
    SELECT 
        TopLevelMaterial AS 顶级物料,
        LevelCode AS 层级编码,
        SortCode AS 排序编码,
        ParentMaterial AS 上级物料,
        LeafMaterial AS 子物料,
        BOMUsage AS 单层用量,
        TotalUsage AS 总成用量,
        Level AS 层级
    FROM dbo.BOM_Result
    WHERE UserName = @UserName
      AND QueryID = @QueryID
    ORDER BY SortCode
    OPTION (RECOMPILE);

END TRY
BEGIN CATCH
    -- 返回空结果集
    SELECT TOP 0 
        NULL AS TopLevelMaterial,
        NULL AS LevelCode,
        NULL AS SortCode,
        NULL AS ParentMaterial,
        NULL AS LeafMaterial,
        NULL AS BOMUsage,
        NULL AS TotalUsage,
        NULL AS Level;
END CATCH

END
GO

3.2 反向查询存储过程

USE [ZKZN]
GO
/** Object: StoredProcedure [dbo].[usp_GetBOM_Reverse] Script Date: 2025/12/12 16:00:00 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 反向查询存储过程：返回所有包含指定子物料的完整BOM
CREATE OR ALTER PROCEDURE [dbo].[usp_GetBOM_Reverse]
@FullParam NVARCHAR(150) -- 格式："A1子物料编码$用户名"
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @LeafMaterialCode NVARCHAR(100);
DECLARE @UserName NVARCHAR(50);
DECLARE @QueryID NVARCHAR(50);
DECLARE @DollarIndex INT;
DECLARE @InsertedRows INT = 0;

BEGIN TRY
    -- 1. 参数解析
    SET @FullParam = LTRIM(RTRIM(@FullParam));

    IF @FullParam = '' OR LEFT(@FullParam, 1) <> 'A'
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 去掉"A1"前缀
    DECLARE @ParamWithoutPrefix NVARCHAR(150) = SUBSTRING(@FullParam, 3, LEN(@FullParam) - 2);
    SET @DollarIndex = CHARINDEX('$', @ParamWithoutPrefix);

    IF @DollarIndex <= 0
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    SET @LeafMaterialCode = LEFT(@ParamWithoutPrefix, @DollarIndex - 1);
    SET @UserName = SUBSTRING(@ParamWithoutPrefix, @DollarIndex + 1, LEN(@ParamWithoutPrefix));
    SET @LeafMaterialCode = LTRIM(RTRIM(@LeafMaterialCode));
    SET @UserName = LTRIM(RTRIM(@UserName));

    IF LEN(@LeafMaterialCode) = 0 OR LEN(@UserName) = 0
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 检查物料是否存在
    IF NOT EXISTS (SELECT 1 FROM dbo.tabDIYTable12746 WHERE F111882 = @LeafMaterialCode)
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 生成查询ID
    SET @QueryID = LEFT(REPLACE(CAST(NEWID() AS NVARCHAR(50)), '-', ''), 8);

    -- 删除用户历史数据
    DELETE FROM dbo.BOM_Result WHERE UserName = @UserName;

    -- 2. 使用ZJBOM2_zjwl函数直接获取所有包含该子物料的完整BOM
    INSERT INTO dbo.BOM_Result (
        UserName,
        MaterialCode,
        QueryID,
        TopLevelMaterial,
        LevelCode,
        SortCode,
        ParentMaterial,
        LeafMaterial,
        BOMUsage,
        TotalUsage,
        Level
    )
    SELECT 
        @UserName,
        @LeafMaterialCode,
        @QueryID,
        TopLevelMaterial,
        LevelCode,
        SortCode,
        ParentMaterial,
        LeafMaterial,
        BOMUsage,
        TotalUsage,
        Level
    FROM dbo.ZJBOM2_zjwl(@LeafMaterialCode)
    ORDER BY TopLevelMaterial, SortCode
    OPTION (RECOMPILE);

    SET @InsertedRows = @@ROWCOUNT;

    -- 3. 如果ZJBOM2_zjwl没有返回结果，检查子件自身是否是顶级物料
    IF @InsertedRows = 0
    BEGIN
        -- 检查子件自身是否是顶级物料
        INSERT INTO dbo.BOM_Result (
            UserName,
            MaterialCode,
            QueryID,
            TopLevelMaterial,
            LevelCode,
            SortCode,
            ParentMaterial,
            LeafMaterial,
            BOMUsage,
            TotalUsage,
            Level
        )
        SELECT 
            @UserName,
            @LeafMaterialCode,
            @QueryID,
            TopLevelMaterial,
            LevelCode,
            SortCode,
            ParentMaterial,
            LeafMaterial,
            BOMUsage,
            TotalUsage,
            Level
        FROM dbo.ZJBOM2_djwl(@LeafMaterialCode)
        ORDER BY SortCode
        OPTION (MAXRECURSION 15, RECOMPILE);

        SET @InsertedRows = @@ROWCOUNT;
    END

    -- 4. 返回结果
    IF @InsertedRows > 0
    BEGIN
        -- 首先返回所有涉及的顶级物料
        SELECT DISTINCT 
            TopLevelMaterial AS 使用该子物料的顶级物料
        FROM dbo.BOM_Result
        WHERE UserName = @UserName
          AND QueryID = @QueryID
        ORDER BY TopLevelMaterial;

        -- 然后返回完整的BOM结构
        SELECT 
            TopLevelMaterial AS 顶级物料,
            LevelCode AS 层级编码,
            SortCode AS 排序编码,
            ParentMaterial AS 上级物料,
            LeafMaterial AS 子物料,
            BOMUsage AS 单层用量,
            TotalUsage AS 总成用量,
            Level AS 层级
        FROM dbo.BOM_Result
        WHERE UserName = @UserName
          AND QueryID = @QueryID
        ORDER BY TopLevelMaterial, SortCode;
    END
    ELSE
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
    END

END TRY
BEGIN CATCH
    -- 返回空结果集
    SELECT TOP 0 
        NULL AS TopLevelMaterial,
        NULL AS LevelCode,
        NULL AS SortCode,
        NULL AS ParentMaterial,
        NULL AS LeafMaterial,
        NULL AS BOMUsage,
        NULL AS TotalUsage,
        NULL AS Level;
END CATCH

END
GO

3.3 统一入口存储过程

USE [ZKZN]
GO
/** Object: StoredProcedure [dbo].[usp_GetBOM] Script Date: 2025/12/12 16:00:00 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 统一的BOM查询存储过程
CREATE OR ALTER PROCEDURE [dbo].[usp_GetBOM]
@FullParam NVARCHAR(150) -- 格式："A0物料编码$用户名" 或 "A1物料编码$用户名"
AS
BEGIN
SET NOCOUNT ON;

DECLARE @QueryType CHAR(1);

BEGIN TRY
    -- 1. 基本参数验证
    IF LEN(@FullParam) < 5
    BEGIN
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
        RETURN;
    END

    -- 2. 快速解析查询类型
    SET @QueryType = SUBSTRING(@FullParam, 2, 1);

    -- 3. 根据查询类型调用对应的存储过程
    IF @QueryType = '0'
    BEGIN
        -- 正向查询
        EXEC [dbo].[usp_GetBOM_Forward] @FullParam = @FullParam;
    END
    ELSE IF @QueryType = '1'
    BEGIN
        -- 反向查询
        EXEC [dbo].[usp_GetBOM_Reverse] @FullParam = @FullParam;
    END
    ELSE
    BEGIN
        -- 无效的查询类型
        SELECT TOP 0 
            NULL AS TopLevelMaterial,
            NULL AS LevelCode,
            NULL AS SortCode,
            NULL AS ParentMaterial,
            NULL AS LeafMaterial,
            NULL AS BOMUsage,
            NULL AS TotalUsage,
            NULL AS Level;
    END

END TRY
BEGIN CATCH
    -- 返回空结果
    SELECT TOP 0 
        NULL AS TopLevelMaterial,
        NULL AS LevelCode,
        NULL AS SortCode,
        NULL AS ParentMaterial,
        NULL AS LeafMaterial,
        NULL AS BOMUsage,
        NULL AS TotalUsage,
        NULL AS Level;
END CATCH

END
GO

索引优化

USE [ZKZN]
GO

-- 4.1 基础表索引
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_tabDIYTable12746_F111882' AND object_id = OBJECT_ID('dbo.tabDIYTable12746'))
BEGIN
CREATE NONCLUSTERED INDEX IX_tabDIYTable12746_F111882
ON dbo.tabDIYTable12746 (F111882)
INCLUDE (F111904, F111891)
WITH (ONLINE = ON, MAXDOP = 4);
PRINT '已创建索引: IX_tabDIYTable12746_F111882';
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_tabDIYTable12746_F111904' AND object_id = OBJECT_ID('dbo.tabDIYTable12746'))
BEGIN
CREATE NONCLUSTERED INDEX IX_tabDIYTable12746_F111904
ON dbo.tabDIYTable12746 (F111904)
INCLUDE (F111882, F111891)
WITH (ONLINE = ON, MAXDOP = 4);
PRINT '已创建索引: IX_tabDIYTable12746_F111904';
END
GO

-- 4.2 结果表索引
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_BOM_Result_UserName_QueryID' AND object_id = OBJECT_ID('dbo.BOM_Result'))
BEGIN
CREATE NONCLUSTERED INDEX IX_BOM_Result_UserName_QueryID
ON dbo.BOM_Result (UserName, QueryID)
INCLUDE (TopLevelMaterial, LeafMaterial, Level, SortCode)
WITH (ONLINE = ON, MAXDOP = 4);
PRINT '已创建索引: IX_BOM_Result_UserName_QueryID';
END
GO

使用说明

5.1 参数格式

• 正向查询：A0物料编码$用户名

• 示例：A000002002A$user1

• 功能：查询指定物料的完整BOM结构

• 返回：一个结果集，包含完整的BOM数据

• 反向查询：A1物料编码$用户名

• 示例：A1000082003U$user2

• 功能：查询所有包含指定子物料的完整BOM结构

• 返回：两个结果集

1. 使用该子物料的顶级物料列表
2. 完整的BOM结构

5.2 调用方式

-- 统一入口（推荐）
EXEC [dbo].[usp_GetBOM] 'A000002002A$user1'; -- 正向查询
EXEC [dbo].[usp_GetBOM] 'A1000082003U$user2'; -- 反向查询

-- 或者直接调用专用存储过程
EXEC [dbo].[usp_GetBOM_Forward] 'A000002002A$user1';
EXEC [dbo].[usp_GetBOM_Reverse] 'A1000082003U$user2';

验证脚本

-- 1. 验证正向查询
PRINT '===== 正向查询验证 =====';
EXEC [dbo].[usp_GetBOM] 'A0000082002U$test_user_forward';
GO

-- 2. 验证反向查询
PRINT '===== 反向查询验证 =====';
EXEC [dbo].[usp_GetBOM] 'A1000082001U$test_user_reverse';
GO

-- 3. 检查结果表
PRINT '===== 检查结果表 =====';
SELECT
UserName,
MaterialCode AS 查询物料,
QueryID,
COUNT(*) AS 记录数,
COUNT(DISTINCT TopLevelMaterial) AS 顶级物料数
FROM dbo.BOM_Result
WHERE UserName IN ('test_user_forward', 'test_user_reverse')
GROUP BY UserName, MaterialCode, QueryID
ORDER BY UserName, QueryID;
GO

部署顺序
确保 ZJBOM2() 基础函数已存在
创建结果表 BOM_Result（如果不存在）
创建索引
创建正向查询函数 ZJBOM2_djwl
创建反向查询函数 ZJBOM2_zjwl
创建正向查询存储过程 usp_GetBOM_Forward
创建反向查询存储过程 usp_GetBOM_Reverse
创建统一入口存储过程 usp_GetBOM
运行验证脚本
系统特点
架构清晰：函数分层，逻辑分离
性能优化：使用索引和查询提示
结果缓存：查询结果写入结果表
错误处理：完善的错误处理机制
接口统一：单参数格式，支持正向和反向查询
易于维护：代码结构清晰，便于修改和扩展
维护建议
定期清理：设置作业定期清理 BOM_Result 表中的历史数据
监控性能：定期检查查询执行计划
更新统计：定期更新表统计信息
索引维护：定期重建索引

这个完整方案已经经过测试，可以稳定运行。如果您需要增加其他功能（如导出、权限控制等），可以在现有架构基础上进行扩展。

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

对表值函数的传参查询方案

近期文章

近期评论

对表值函数的传参查询方案

相关文章

SQL查询所有表最近一分钟的修改情况/更新表单锁定状态

动态SQL当表名和字段名是变量时

成套BOM组件序号查询，用于成套BOM导入自动新增组件BOM

给数据规范和内源名称自动加ID

近期文章

近期评论