Tsql在存储过程中执行存储过程，以逗号分隔的列表作为参数

Question

我的问题是:

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

@location = '3380,4407'

当我硬编码它工作:

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

当我用相同的值传递@location时，它不起作用。

ALTER   Procedure [svr].[spSvrBranchReviewsGet]
@Location nVARCHAR(Max),            --Comma delimited string or All
@PayGroup nVARCHAR(Max),            --Comma delimited string  or All
@ApprovalStatus VARCHAR(10),        --Yes, No or All
@PayDate nVARCHAR(Max),             --Comma delimited string or All
@Division_ID VARCHAR(1000) = NULL,
@UserID INT = NULL,
@IsAdminCorp BIT                    --Admin or not
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

---

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

---

Full Proc:
ALTER   Procedure [svr].[spSvrBranchReviewsGet]
@Location nVARCHAR(Max),            --Comma delimited string or All
@PayGroup nVARCHAR(Max),            --Comma delimited string  or All
@ApprovalStatus VARCHAR(10),        --Yes, No or All
@PayDate nVARCHAR(Max),             --Comma delimited string or All
@Division_ID VARCHAR(1000) = NULL,
@UserID INT = NULL,
@IsAdminCorp BIT                    --Admin or not
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

Answer 1

很明显为什么它不起作用。在spSvrPayDateGet_V1中，将变量设置为:

SET @Location = '''' + SUBSTRING(@UserBranchesList, 1, (LEN(@UserBranchesList)-1))  + ''''

开头和结尾的引号成为字符串的一部分，如:"'3380,4407'"请注意，这与将变量设置为'3380,4407'不同，后者只是&;3380,4407&;

当创建字符串值时，"不应该被附加到字符串本身。只使用:

SET @Location = SUBSTRING(@UserBranchesList, 1, (LEN(@UserBranchesList)-1))