0

I am stuck on what is probably a simple thing, but can't figure it out.

I have the query below which gives me the results I want - two records are returned.

SELECT * 
FROM tbl_BudgetScenario
WHERE Name IN ('Sewer Pipes for 100 Years',
               'Sanitary Pipe - $5M for 50 Years')

However, if I place the IN statement parameters into a variable (which I need to do for the code I am working on), I don't get any results.

DECLARE @Scenarios VARCHAR(MAX)

SET @Scenarios = '''Sewer Pipes for 100 Years''' + ','+ '''Sanitary Pipe - $5M for 50 Years'''

SELECT * 
FROM tbl_BudgetScenario
WHERE Name IN (@Scenarios)

It isn't obvious to me why this wouldn't work. Hoping someone can explain and/or provide a potential workaround.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Carl Brydon
  • 83
  • 1
  • 5
  • 2
    It **IS** obvious - `IN` expects a **list** of values - which is what you provide in your first example. In your second example, you're providing only *a SINGLE string* - no longer a list - therefore `IN` doesn't work anymore..... just because you have multiple values in your string, separated by a comma doesn't make a single string magically be a list ...... – marc_s Aug 06 '21 at 13:42
  • 1
    A workaround could be to insert the values into a (temp)table, and join that in your query – HoneyBadger Aug 06 '21 at 13:44
  • OK Thanks @marc_s - dumb question... Is there a way to convert a single string with multiple values separated by a comma into a list? – Carl Brydon Aug 06 '21 at 14:01
  • Thanks @HoneyBadger, I'll give that a try. – Carl Brydon Aug 06 '21 at 14:02
  • 1
    Yes - use `STRING_SPLIT` - that's what it's there for - available in SQL Server **2016** and newer (you didn't mention what *version* of SQL Server this is for) – marc_s Aug 06 '21 at 14:03
  • Thanks @marc_s - I'll give that a go. – Carl Brydon Aug 06 '21 at 14:11
  • 1
    @marc_s String_Split worked nicely - Thanks for the guidance! – Carl Brydon Aug 06 '21 at 14:13
  • I strongly suggest you use a Table Parameter, table variable or temp table, instead of `STRING_SPLIT` – Charlieface Aug 06 '21 at 15:37

0 Answers0