Simple query that should be working is not-CodePudding

I have a simple query:

declare @manual varchar(80) = '''Discount'',''Misc Charges'''
select *
from #Final 
where charge_type in (@manual)

Now I've gone as far as verifying my declared variable is setup correctly by using the PRINT command as follows: PRINT '''Discount'',''Misc Charges''' and it in fact returns as I would expect: 'Discount','Misc Charges'.

However, when I run this query, I get no results.

If I instead simply use:

select *
from #Final 
where charge_type in ('Discount','Misc Charges')

Then no problem, I get my results. I'm sure I'll kick myself once I get the answer, but as of right now, this is just not making sense. No errors, it's just not giving me my columns without any rows as if there's no data. What am I missing?

CodePudding user response：

Because

IN ('''Discount'',''Misc Charges''')

is the same as

= '''Discount'',''Misc Charges'''

In other words, that is one single string that contains a bunch of escaped string delimiters, not a comma-separated list of individual string values. Which is why you can do this without SQL Server barfing:

PRINT '''Discount'',''Misc Charges''';

What you want is:

declare @manual varchar(80) = 'Discount,Misc Charges';

select f.*
from #Final AS f
INNER JOIN STRING_SPLIT(@manual, ',') AS s
ON f.charge_type = s.value;

However that can fail on compatibility_level < 130, in which case:

declare @manual varchar(80) = 'Discount,Misc Charges';

select f.*
from #Final AS f
INNER JOIN 
OPENJSON('["'   REPLACE(@manual, ',', '","')   '"]') AS s
ON f.charge_type = s.value;

In the latter case you can make the query itself a little nicer by using slightly different jacked-up strings in the variable declaration:

declare @manual varchar(80) = '["Discount","Misc Charges"]';

select f.*
from #Final AS f
INNER JOIN 
OPENJSON(@manual) AS s ON f.charge_type = s.value;

Or if you are on an older version and you really are hand-crafting these strings inline, you can use a table variable or CTE like @SMor suggested.

Table variable:

DECLARE @d table(str varchar(32));
INSERT @d VALUES('Discount'),('Misc Charges');

SELECT f.*
from #Final AS f
INNER JOIN @d AS d
ON f.charge_type = d.str;

CTE:

;WITH cte AS 
(
  SELECT str = 'Discount'
  UNION ALL 
  SELECT str = 'Misc Charges'
)
SELECT f.*
from #Final AS f
INNER JOIN cte
ON f.charge_type = cte.str;

If you'll have more values at some point, it tips to writing a table constructor instead of multiple UNION ALLs, e.g.

;WITH cte AS 
(
  SELECT str FROM 
  (
    VALUES('Discount','Misc Charges')
  ) AS s(str)
)
SELECT f.*
from #Final AS f
INNER JOIN cte
ON f.charge_type = cte.str;

CodePudding user response：

You can use just use your list of values as comma seperated string & then use STRING_SPLIT.

declare @manual varchar(80) = 'Discount,Misc Charges'
select *from #Final 
where charge_type in (SELECT * from STRING_SPLIT(@manual,',))

CodePudding user response：

Here is to to do in SQL Server 2016 onwards.

SQL

DECLARE @manual VARCHAR(80) = 'Discount,Misc Charges';

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, charge_type VARCHAR(30));
INSERT INTO @tbl (charge_type) VALUES
('Discount'),
('No Discount'),
('Misc Charges');

SELECT *
FROM @tbl 
WHERE charge_type in (SELECT value FROM STRING_SPLIT(@manual, ','))