Concatenate string inside a in clause sql server

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)


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?


@Value would be: 'Los Angeles' + ',' +'San Diego'+ ','+'Fresno'
inside the in clause:
AND gsrf.Charter in (@Value)



Los Angeles,San Diego,Fresno


2 Answers
2

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 |
+-------------+


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')


'All'

'All'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.