我的问题是:
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
很明显为什么它不起作用。在spSvrPayDateGet_V1中,将变量设置为:
SET @Location = '''' + SUBSTRING(@UserBranchesList, 1, (LEN(@UserBranchesList)-1)) + ''''
开头和结尾的引号成为字符串的一部分,如:"'3380,4407'"请注意,这与将变量设置为'3380,4407'不同,后者只是&;3380,4407&;
当创建字符串值时,"不应该被附加到字符串本身。只使用:
SET @Location = SUBSTRING(@UserBranchesList, 1, (LEN(@UserBranchesList)-1))