0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
jw11432
  • 545
  • 2
  • 20

3 Answers3

4

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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I think what's confusing me is that this has been the method I've always used for my debugging. So something changed in SQL that now disallows the way I'm declaring my variables? This all comes back to trying to get an SSRS report to run with multiple variables selected in the drop-down. Upon selecting various variables, I'm getting an error: "An expression of non-boolean type specified in a context where a condition is expected, near ','." So this comes down to a recent change right? – jw11432 Jan 07 '22 at 20:42
  • @jw11432 Nope, this is not a recent change. You would never have been able to `DECLARE @manual varchar(80)` and then have SQL Server process that as a set. That's a single, scalar value. Maybe what you did before is append your string to dynamic SQL or built the string that way in the client application. Also I don't know what you mean by `Upon selecting various variables`, could you set up a [db<>fiddle](https://dbfiddle.uk) to demonstrate. – Aaron Bertrand Jan 07 '22 at 20:44
  • Man, I know for a fact I've done it. I very vividly remember doing it. I haven't had to debug like this for a few months now, which is when I'm assuming this "change" happened, but I definitely did. My head is spinning from this. I appreciate your time, I guess I need to go back and find an example (hopefully one exists) where I did this so I can see where I might have gone wrong now. It seems you must be right, but I'm just floored by this as I would've bet my house on this working before now. – jw11432 Jan 07 '22 at 20:58
  • Perhaps you confuse your previous attempts that use a table variable and not a scalar variable? If you populate a table variable, then you can do exactly what you attempted and you don't need this obtuse string splitting. You could also use a CTE as a replacement for the table variable or use a [table value constructor](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15). – SMor Jan 07 '22 at 21:09
  • @AaronBertrand OP started with a trivial script using a scalar variable. A simple change to a table variable seems much more feasible for a person struggling with this issue. OP does refer to "debugging" which implies (to me) this is all hand-written. – SMor Jan 07 '22 at 21:28
  • @SMor Thanks, yes it is indeed all hand written. I like to do my report building in T-SQL first, then copy/paste into SSRS, hence this approach. My memory could be recalling a different scenario that I'm false equivocating, one in which I used escapes for passing through values within a linked server query...and that could be what I'm remembering and not actually having done that with declared scalar variables. – jw11432 Jan 07 '22 at 21:33
  • @SMor point taken, updated – Aaron Bertrand Jan 07 '22 at 21:40
  • @jw11432 - I've heard similar confusion from people using SSRS before. Perhaps you were using this approach? https://stackoverflow.com/a/519573/73226 - I'm not entirely sure if/how this works but if it does work at all I assume the `where yourColumn in (@)` must get expanded out to `where yourColumn in (1,2,3)` before the query gets sent to the server. - i.e. it is referencing a reporting services parameter not a SQL parameter – Martin Smith Jan 08 '22 at 15:34
  • @jw11432 Indeed this is what SSRS does, however you *cannot* do this in T-SQL directly. Use a table variable as mentioned – Charlieface Jan 09 '22 at 01:50
0

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, ','))
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
0

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,',))
Rafiq Nazir
  • 117
  • 4