Concatenate string inside a in clause sql server

Question

I'm trying to concatenate a string inside a sql statement. I'm using sql server and this is how it looks;
declare @SchoolID INT ,@Transaction nVarChar(35) ,@Reporting nVarChar(35) ,@SchoolOf nVarChar(35) ,@SchoolYear INT ,@Charter Varchar(25) ,@Value varchar(max)

SET @SchoolID   = 105           -- HS - 65, MS - 66, ES - 67
SET @TransactionTypeCode = ''   -- '', 'D', 'R'
SET @ReportingLEA = '1'
SET @SchoolOfAttendance = '1' -- HS - 1930056, MS - 6061238, ES - 6010862
SET @SchoolYear =295
SET @Charter = 'ALL'
SET @Value = 
            CASE @Charter
                WHEN 'Los Angeles'
                    THEN 'Los Angeles'
                WHEN'San Diego'
                    THEN 'San Diego'
                WHEN 'Fresno'
                    THEN 'Fresno'                           
                WHEN 'ALL'
                    THEN 'Los Angeles' + ',' +'San Diego'+ ','+'Fresno' --trying to concatenate data to show in the clause IN like charter IN ('Los Angeles','San Diego','Fresno')
            END

 print @Value

What i want is that if the user selects 'All' then the value of @Value would be: 'Los Angeles' + ',' +'San Diego'+ ','+'Fresno' inside the in clause: AND gsrf.Charter in (@Value) But is not working. I believe the in clause doesn't support this kind of concatenation If run this query and set the variable to 'ALL' then i get this in the print result: Los Angeles,San Diego,Fresno But if i run the whole script with this results (without the IN separation) i don't get any data. Any suggestions?

Possible duplicate of [T-SQL stored procedure that accepts multiple Id values](https://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) — Eric Brandt, Jul 26 '18 at 03:03

score 0 · Answer 1 · answered Jul 25 '18 at 21:55

0

I don't know what you are doing with the variables, but I think the logic you want is more like:

where (Charter = @Charter or @Charter = 'All')

answered Jul 25 '18 at 21:55

Gordon Linoff

1,242,037
58
646
786

There's no 'ALL' charter charter must include: Los Angeles, San Diego, Fresno. when the user selects ALL But in database there no any row like ALL – rdi0r Jul 25 '18 at 21:58
@rdi0r . . . That has no comparison of `'All'` to a database column; `'All'` is in the *parameter*, and it would choose all rows (presumably that is the intent). – Gordon Linoff Jul 26 '18 at 02:21

critical_error · Accepted Answer · 2018-07-25T22:35:13.643

You cannot use a comma-delimited string variable with SQL's IN selector without executing a dynamic statement.

Having said that, you can achieve your goal by changing your @Value type to XML and then querying the XML in place of the comma-delimited string.

So, for exammple:

SET @Value = CASE @Charter
    WHEN 'Los Angeles'
        THEN '<cities><city>Los Angeles</city></cities>'
    WHEN'San Diego'
        THEN '<cities><city>San Diego</city></cities>'
    WHEN 'Fresno'
        THEN '<cities><city>Fresno</city></cities>'                           
    WHEN 'ALL'
        THEN '<cities><city>Los Angeles</city><city>San Diego</city><city>Fresno</city></cities>'
END

And then use @Value with your IN selector:

...AND gsrf.Charter IN (

    SELECT x.f.value( '.', 'VARCHAR(35)' ) AS City FROM @Value.nodes( '//cities/city' ) x( f )

 );

Using your "ALL" flag would return the following from @Value:

+-------------+
|    City     |
+-------------+
| Los Angeles |
| San Diego   |
| Fresno      |
+-------------+

Thanks man! I knew it, that i couldn't use it like that. I was wondering if there was any other chance to do it. — rdi0r, Jul 25 '18 at 22:36
You're welcome, @rdi0r. Don't forget to mark it as the answer if this resolves your question. — critical_error, Jul 25 '18 at 22:38

Concatenate string inside a in clause sql server

2 Answers2