Here is my issue:
INSERT INTO #PayGroupList EXEC CareCentral.[svr].[spSvrPayGroupsGet_V1] @Location
@location = '3380,4407'
When i hard code it works:
INSERT INTO #PayGroupList EXEC CareCentral.[svr].[spSvrPayGroupsGet_V1] '3380,4407'
when i pass the @location with the same value it does not work.
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
The insert Statement is not populating the temptable. #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
1条答案
按热度按时间lstz6jyr1#
Well, it's clear why it doesn't work. In the spSvrPayDateGet_V1, you set the variable to:
The leading and ending quotes becomes part of the string like: "'3380,4407'". Note that it's not the same as setting variable to '3380,4407', which is just "3380,4407".
When creating string values, the '' shouldn't be appended to the string itself. Just use: