T-SQL存储过程内调用存储过程：逗号分隔参数致临时表填充失效

执行以下语句时，当@Location = '3380,4407'，临时表#PayGroupList无法被填充：

INSERT INTO #PayGroupList EXEC CareCentral.[svr].[spSvrPayGroupsGet_V1] @Location

但硬编码参数值执行时，临时表可正常填充：

INSERT INTO #PayGroupList EXEC CareCentral.[svr].[spSvrPayGroupsGet_V1] '3380,4407'

存储过程 [svr].[spSvrBranchReviewsGet]（片段） #

ALTER Procedure [svr].[spSvrBranchReviewsGet]
@Location nVARCHAR(Max),            --逗号分隔字符串或All
@PayGroup nVARCHAR(Max),            --逗号分隔字符串或All
@ApprovalStatus VARCHAR(10),        --Yes, No或All
@PayDate nVARCHAR(Max),             --逗号分隔字符串或All
@Division_ID VARCHAR(1000) = NULL,
@UserID INT = NULL,
@IsAdminCorp BIT                    --是否为管理员

AS
Set NoCount ON

IF (@PayGroup = 'All')
    BEGIN
    
        DECLARE @PayGroupList NVARCHAR(MAX)= ''
        
        IF OBJECT_ID('tempdb.dbo.#PayGroupList') IS NOT NULL
        BEGIN
            DROP TABLE #PayGroupList;
        END
    
        CREATE TABLE #PayGroupList( PayGroupId NVARCHAR(100) );

        SELECT @Location

        INSERT INTO #PayGroupList EXEC CareCentral.[svr].[spSvrPayGroupsGet_V1] @Location

        SELECT * FROM #PayGroupList;

        SELECT @PayGroupList = @PayGroupList + PayGroupId +  N',' FROM #PayGroupList;

        IF (LEN(@PayGroupList) > 0)
        BEGIN
            SET @PayGroup = '''' + SUBSTRING(@PayGroupList, 1, (LEN(@PayGroupList)-1))  + ''''
        END

        SELECT @PayGroup;
    END

问题点：上述INSERT语句无法填充临时表#PayGroupList

存储过程 [svr].[spSvrPayDateGet_V1] #

Alter Procedure [svr].[spSvrPayDateGet_V1]
@LocationList NVARCHAR(MAX),
@PayGroupList NVARCHAR(MAX)
AS

SET NOCOUNT ON

BEGIN

    WITH ctePayDates
    AS
    (
        SELECT DISTINCT Location, 
        LocationId = CASE   
                WHEN Location='111111' THEN CAST(LEFT(Location, LEN(Location) -2) AS NVARCHAR(50))
                WHEN SUBSTRING(Location, 5, 1) <> '0' THEN CAST(LEFT(Location, LEN(Location) -1) AS NVARCHAR(50))
                ELSE CAST(LEFT(Location, LEN(Location) -2) AS nvarchar(50))
                END,
        PayGroup, PayDate
        FROM Carecentral.svr.Summary  
    )

        SELECT DISTINCT PayDate FROM ctePayDates 
        WHERE LocationId IN (Select value from String_Split(@LocationList, ',')) AND PayGroup IN (Select value from String_Split(@PayGroupList, ','))

END

完整存储过程 [svr].[spSvrBranchReviewsGet] #

ALTER Procedure [svr].[spSvrBranchReviewsGet]
@Location nVARCHAR(Max),            --逗号分隔字符串或All
@PayGroup nVARCHAR(Max),            --逗号分隔字符串或All
@ApprovalStatus VARCHAR(10),        --Yes, No或All
@PayDate nVARCHAR(Max),             --逗号分隔字符串或All
@Division_ID VARCHAR(1000) = NULL,
@UserID INT = NULL,
@IsAdminCorp BIT                    --是否为管理员

AS
Set NoCount ON

IF (@Location = 'All')
    BEGIN

        DECLARE @UserBranchesList NVARCHAR(MAX)= ''
        
        IF OBJECT_ID('tempdb.dbo.#UserBranchesList') IS NOT NULL
        BEGIN
            DROP TABLE #UserBranchesList;
        END
    
        CREATE TABLE #UserBranchesList( BranchId NVARCHAR(100), BranchName NVARCHAR(100)    )

        INSERT INTO #UserBranchesList EXEC [svr].[spSvrUserBranchesGET] @Division_ID, @UserID ,@IsAdminCorp

        SELECT @UserBranchesList = @UserBranchesList + BranchId + N',' FROM #UserBranchesList WHERE BranchId IS NOT NULL 

        IF (LEN(@UserBranchesList) > 0)
        BEGIN
            SET @Location = '''' + SUBSTRING(@UserBranchesList, 1, (LEN(@UserBranchesList)-1))  + ''''
        END

        SELECT @Location

    END

IF (@PayGroup = 'All')
    BEGIN
    
        DECLARE @PayGroupList NVARCHAR(MAX)= ''
        
        IF OBJECT_ID('tempdb.dbo.#PayGroupList') IS NOT NULL
        BEGIN
            DROP TABLE #PayGroupList;
        END
    
        CREATE TABLE #PayGroupList( PayGroupId NVARCHAR(100) );

        SELECT @Location

        INSERT INTO #PayGroupList EXEC CareCentral.[svr].[spSvrPayGroupsGet_V1] @Location

        SELECT * FROM #PayGroupList;

        SELECT @PayGroupList = @PayGroupList + PayGroupId +  N',' FROM #PayGroupList;

        IF (LEN(@PayGroupList) > 0)
        BEGIN
            SET @PayGroup = '''' + SUBSTRING(@PayGroupList, 1, (LEN(@PayGroupList)-1))  + ''''
        END

        SELECT @PayGroup;
    END

IF (@PayDate = 'All')
    BEGIN

        DECLARE @PayDateList NVARCHAR(MAX)=''
        
        IF OBJECT_ID('tempdb.dbo.#PayDateList') IS NOT NULL
        BEGIN
            DROP TABLE #PayDateList;
        END
    
        CREATE TABLE #PayDateList( PayDate VARCHAR(100) )

        INSERT INTO #PayDateList EXEC CareCentral.[svr].[spSvrPayDateGet_V1] @Location, @PayGroup

        SELECT * FROM #PayDateList

        SELECT @PayDateList = @PayDateList + PayDate + N',' FROM #PayDateList       

        SELECT @PayDateList

        IF (LEN(@PayDateList) > 0)
        BEGIN
            SET @PayDate = '''' + SUBSTRING(@PayDateList, 1, (LEN(@PayDateList)-1)) + ''''
        END

        SELECT @PayDate

    END

问题原因及解决方案 #

核心原因 #

从完整存储过程代码可见，当@Location = 'All'时，代码会将@Location重新赋值为带单引号的字符串（例如'3380,4407'）。此时调用spSvrPayGroupsGet_V1时，传入的参数包含额外单引号，若该存储过程用STRING_SPLIT处理参数，会拆分出'3380和4407'（带单引号），与实际LocationId不匹配，导致返回空结果，临时表无法填充。

而硬编码'3380,4407'时，传入的是不带额外单引号的纯逗号分隔字符串，拆分后得到正确的3380和4407，因此能正常返回数据。

解决方案 #

1. 修正@Location赋值逻辑：处理@Location = 'All'时，不要给拼接后的字符串添加单引号，直接保留纯ID逗号分隔字符串：

IF (LEN(@UserBranchesList) > 0)
BEGIN
    SET @Location = SUBSTRING(@UserBranchesList, 1, (LEN(@UserBranchesList)-1))
END

2. 统一参数格式：所有依赖逗号分隔字符串的存储过程，均使用纯ID拼接格式（无单引号），避免格式不一致导致查询失败。
3. 检查spSvrPayGroupsGet_V1逻辑：确保该存储过程能正确处理纯逗号分隔字符串参数，拆分后得到正确的ID值。

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