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 NOCOU

Solution 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@Extendedvarchar(max)='title1,titl2'

Here is a simple

DECLARE@Extendedvarchar(max)='title1,titl2'select*,row_number() over(orderby (selectNULL)) 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@parameter1varchar(50) 
 declare@parameter2varchar(50) 
 ;with cte as (
   select*,row_number() over(orderby (selectNULL)) rn
   from STRING_SPLIT (@Extended,',')
 ) 
 select@parameter1=MAX(casewhen rn =1thenvalueend),
        @parameter2=MAX(casewhen rn =2thenvalueend)
 from cte

sqlfiddle

Solution 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 :

SELECTLTRIM(RTRIM(m.n.value('.[1]','varchar(8000)')))
FROM (
    SELECT CAST('<XMLRoot><RowData>' + REPLACE(@Extended,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) Extended
) DCROSSAPPLYExtended.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.

CREATEFUNCTIONSplitToRows 
(   
    @Extended   VARCHAR(MAX)
)
RETURNSTABLEASRETURN 
(
    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           =0ASSET NOCOUNT ON
        IF(@All=0)
        BEGINSELECT
                 [GeneralKey]
                , [Label]
                 FROM [General]
                 WHERE 
                     [Extended] IN( SELECT*FROM dbo.SplitToRows(@Extended) )
                 AND [Label] NOTIN( SELECT*FROM dbo.SplitToRows(@Exclude) ) 
                 ORDERBY
                        [OrderID];
        ENDELSEBEGIN
            IF(@All=1)
            BEGINSELECT0AS [GeneralKey]
                   , 'Any'AS [Label]
                   , 0AS [OrderID]
               UNIONALLSELECT
                    [GeneralKey]
                   , [Label]
                   , [OrderID]
                    FROM [General]
                    WHERE
                         [Extended] IN( SELECT*FROM dbo.SplitToRows(@Extended) )
                     AND [Label] NOTIN( SELECT*FROM dbo.SplitToRows(@Exclude) ) 
                    ORDERBY
                            [OrderID];
            ENDEND

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.