Sending same parameter twice in exec

问题:

I have a simple stored procedure like this:

[dbo].[getStatusList]
    @Extended NVARCHAR(255) = 'Project Status',
    @Exclude NVARCHAR(255) = '',
    @All BIT = 0
AS
    SET NOCOUNT ON

    IF (@All = 0)
    BEGIN
        SELECT
            [GeneralKey],
            [Label]
        FROM 
            [General]
        WHERE 
            [Extended] = @Extended
            AND [Label] <> @Exclude
        ORDER BY
            [OrderID];
    END
    ELSE
    BEGIN
        IF (@All = 1)
        BEGIN
            SELECT
                0 AS [GeneralKey],
                'Any' AS [Label],
                0 AS [OrderID]
            UNION ALL
            SELECT
                [GeneralKey],
                [Label],
                [OrderID]
            FROM 
                [General]
            WHERE 
                [Extended] = @Extended
                AND [Label] <> @Exclude
            ORDER BY
                [OrderID];
        END
    END

That I want to do is exec this stored procedure sending twice @Extended parameter like:

exec getStatusList @Extended = 'title1' AND @Extended = 'title2'

It is not possible to do something like this on exec? To only way to solve this is to add another parameter to stored procedure?

Update

As comments below mentioned, I tried this:

CREATE OR ALTER PROCEDURE usp_Get_StatusListByDesignType
    -- Add the parameters for the stored procedure here
    @Extended NVARCHAR(MAX),
    @Exclude NVARCHAR(255) = '',
    @All BIT = 0
AS
    SET NOCOUNT ON

    IF (@All = 0)
    BEGIN
        DECLARE  @Parameter1 VARCHAR(50) 
        DECLARE  @Parameter2 VARCHAR(50) 

        ;WITH CTE AS 
        (
             SELECT 
                 *,
                 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn
             FROM 
                 STRING_SPLIT (@Extended,',')
        ) 
        SELECT  
            @Parameter1 = MAX(CASE WHEN rn = 1 THEN VALUE END),
            @Parameter2 = MAX(CASE WHEN rn = 2 THEN VALUE END)
        FROM 
            CTE

        SELECT
            [GeneralKey], [Label]
        FROM 
            [General]
        WHERE 
            [Extended] IN (SELECT @Parameter1, @Parameter2)
            AND [Label] <> @Exclude
        ORDER BY
            [OrderID];
    END
    ELSE
    BEGIN
        IF (@All = 1)
        BEGIN
            SELECT
                0 AS [GeneralKey],
                'Any' AS [Label],
                0 AS [OrderID]
            UNION ALL
            SELECT
                [GeneralKey],
                [Label],
                [OrderID]
            FROM 
                [General]
            WHERE 
                [Extended]  IN (SELECT @Parameter1, @Parameter2)
                AND [Label] <> @Exclude
            ORDER BY
                [OrderID];
        END
        RETURN;

But I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

回答1:

You can let pass your parameter like para1Val1,para1Val2... connected with , comma.

then use STRING_SPLIT function to split it from , comma then get the parameter.

DECLARE @Extended varchar(max)='title1,titl2'

Here is a simple

DECLARE @Extended varchar(max)='title1,titl2'

select *,row_number() over(order by (select NULL)) rn
from STRING_SPLIT (@Extended,',')

Then you can set parameters in SP.

declare parameters variable, then use row_number make your parameter row number.

next step use condition aggregate function set the parameter in select clause.

 declare  @parameter1 varchar(50) 
 declare  @parameter2 varchar(50) 
 ;with cte as (
   select *,row_number() over(order by (select NULL)) rn
   from STRING_SPLIT (@Extended,',')
 ) 
 select @parameter1 = MAX(case when rn = 1 then value end),
        @parameter2 = MAX(case when rn = 2 then value end)
 from cte

sqlfiddle

回答2:

This method :

exec getStatusList @Extended='title1' AND @Extended = 'title2'

it's not going to work at all as a parameter or a variable in general can only hold one value and nothing more. So, you can't do that unless you execute the store procedure twice and specify the parameters on each one of them. Or you may use loops to do it. But i'm not fan of loops and I always suggests to avoid them as much as possible.

The method that I see it fits your situation is a TVP with some modifications on the store procedure itself.

So, you'll pass the values in comma separate values in @Extended and from the store procedure you'll use IN() and NOT IN() instead of = and <> this will extend it to have more values to compare rather than one value.

Then you can use XML to split the values and turn them into rows.

So we will use this :

SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)')))
FROM (
    SELECT CAST('<XMLRoot><RowData>' + REPLACE(@Extended,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) Extended
) D
CROSS APPLY Extended.nodes('/XMLRoot/RowData')m(n)

You can inject it directly into the store procedure with modifying the operators that I mentioned above, and it will work just fine. but for the code reuse, we will use it as TVP.

CREATE FUNCTION SplitToRows 
(   
    @Extended   VARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) Extended
    FROM (
        SELECT CAST('<XMLRoot><RowData>' + REPLACE(@Extended,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) Extended
    ) D
    CROSS APPLY Extended.nodes('/XMLRoot/RowData')m(n)
)

Now, you can modify the store procedure to the following :

[dbo].[getStatusList]
                     @Extended NVARCHAR(255) = 'Project Status'
                    , @Exclude  NVARCHAR(255) = ''
                    , @All      BIT           = 0
AS
        SET NOCOUNT ON
        IF(@All = 0)
        BEGIN
            SELECT
                 [GeneralKey]
                , [Label]
                 FROM [General]
                 WHERE 
                     [Extended] IN( SELECT * FROM dbo.SplitToRows(@Extended) )
                 AND [Label] NOT IN( SELECT * FROM dbo.SplitToRows(@Exclude) ) 
                 ORDER BY
                        [OrderID];
        END
            ELSE
        BEGIN
            IF(@All = 1)
            BEGIN
               SELECT
                    0 AS [GeneralKey]
                   , 'Any' AS [Label]
                   , 0 AS [OrderID]
               UNION ALL
               SELECT
                    [GeneralKey]
                   , [Label]
                   , [OrderID]
                    FROM [General]
                    WHERE
                         [Extended] IN( SELECT * FROM dbo.SplitToRows(@Extended) )
                     AND [Label] NOT IN( SELECT * FROM dbo.SplitToRows(@Exclude) ) 
                    ORDER BY
                            [OrderID];
            END
        END

Now, you can pass multiple separated values in @Extended and @Exclude at the same time like this :

@Extended = 'title1, title2, title3'
@Exclude  = 'title5, title8'

so both parameters will use the same method.