SQL Server存储过程中实现动态条件JOIN查询是否可行？

需求说明 #

你现有一段SQL Server存储过程内的查询语句，执行内连接操作：

SELECT  
    SUM(Amount) as Total
FROM dbo.InvestmentTransactions IT
INNER JOIN dbo.InvestmentEntities IE ON IE.InvestmentEntityId = IT.InvestmentEntityId AND IT.InvestmentEntityId IN (@Id_List)

需求是：当传入的ID列表参数@Id_List为空时，取消IN条件，关联所有数据；当参数不为空时，保留该IN条件。

可行实现方案 #

方案1：在JOIN条件中加入逻辑判断

直接在连接条件里添加分支逻辑，判断参数是否为空：

SELECT  
    SUM(Amount) as Total
FROM dbo.InvestmentTransactions IT
INNER JOIN dbo.InvestmentEntities IE 
    ON IE.InvestmentEntityId = IT.InvestmentEntityId 
    AND (IT.InvestmentEntityId IN (@Id_List) OR @Id_List IS NULL OR @Id_List = '')

• 优点：写法简单，无需额外逻辑
• 缺点：数据量较大时，可能导致查询计划不够优化，因为SQL Server需要兼容两种场景的执行逻辑

方案2：使用动态SQL拼接

通过动态构建SQL语句，根据参数是否为空决定是否添加IN条件：

CREATE PROCEDURE YourProcedureName
    @Id_List NVARCHAR(MAX) -- 假设参数是逗号分隔的ID字符串，如'1,2,3'
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'
    SELECT  
        SUM(Amount) as Total
    FROM dbo.InvestmentTransactions IT
    INNER JOIN dbo.InvestmentEntities IE 
        ON IE.InvestmentEntityId = IT.InvestmentEntityId '

    -- 当参数不为空时拼接IN条件
    IF @Id_List IS NOT NULL AND LTRIM(RTRIM(@Id_List)) != ''
    BEGIN
        SET @SQL += N'AND IT.InvestmentEntityId IN (' + @Id_List + N')'
    END

    -- 执行动态SQL
    EXEC sp_executesql @SQL
END

• 优点：能生成更贴合场景的执行计划，性能更优
• 注意：如果@Id_List来自用户输入，必须做好参数验证，避免SQL注入风险；更安全的做法是配合参数化查询，而非直接拼接字符串

方案3：使用表值参数（推荐）

定义表值类型来传递ID列表，这种方式既安全又高效：

1. 先创建表值类型：

CREATE TYPE IdListType AS TABLE (Id INT)

2. 创建存储过程，使用表值参数：

CREATE PROCEDURE YourProcedureName
    @Id_List IdListType READONLY
AS
BEGIN
    SELECT  
        SUM(Amount) as Total
    FROM dbo.InvestmentTransactions IT
    INNER JOIN dbo.InvestmentEntities IE 
        ON IE.InvestmentEntityId = IT.InvestmentEntityId 
        AND (
            EXISTS(SELECT 1 FROM @Id_List WHERE Id = IT.InvestmentEntityId) 
            OR NOT EXISTS(SELECT 1 FROM @Id_List)
        )
END

• 优点：完全避免SQL注入，查询计划稳定，适合大数据量场景
• 使用方式：调用存储过程时，将ID列表作为表值参数传入

内容的提问来源于stack exchange，提问作者Musaffar Patel