SQL Server Tsql Execute stored procedure inside a Stored procedure with a comma seperated list as parameters

qyzbxkaa  于 2023-04-04  发布在  其他
关注(0)|答案(1)|浏览(131)

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
lstz6jyr

lstz6jyr1#

Well, it's clear why it doesn't work. In the spSvrPayDateGet_V1, you set the variable to:

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

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:

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

相关问题