TSQL Variable With List of Values for IN Clause

Question

I want to use a clause along the lines of "CASE WHEN ... THEN 1 ELSE 0 END" in a select statement. The tricky part is that I need it to work with "value IN @List".

If I hard code the list it works fine - and it performs well:

SELECT
       CASE WHEN t.column_a IN ( 'value a', 'value b' ) THEN 1 ELSE 0 END AS priority
      , t.column_b
      , t.column_c
  FROM
       table AS t
 ORDER BY
       priority DESC

What I would like to do is:

-- @AvailableValues would be a list (array) of strings.
DECLARE
        @AvailableValues ???

 SELECT
        @AvailableValues = ???
   FROM
        lookup_table

 SELECT
        CASE WHEN t.column_a IN @AvailableValues THEN 1 ELSE 0 END AS priority
      , t.column_b
      , t.column_c
   FROM
        table AS t
  ORDER BY
        priority DESC

Unfortunately, it seems that SQL Server doesn't do this - you can't use a variable with an IN clause. So this leaves me with some other options:

1. Make '@AvailableValues' a comma-delimited string and use a LIKE statement. This does not perform well.
2. Use an inline SELECT statement against 'lookup_table' in place of the variable. Again, doesn't perform well (I think) because it has to lookup the table on each row.
3. Write a function wrapping around the SELECT statement in place of the variable. I haven't tried this yet (will try it now) but it seems that it will have the same problem as a direct SELECT statement.
4. ???

Are there any other options? Performance is very important for the query - it has to be really fast as it feeds a real-time search result page (i.e. no caching) for a web site.

Are there any other options here? Is there a way to improve the performance of one of the above options to get good performance?

Thanks in advance for any help given!

UPDATE: I should have mentioned that the 'lookup_table' in the example above is already a table variable. I've also updated the sample queries to better demonstrate how I'm using the clause.

UPDATE II: It occurred to me that the IN clause is operating off an NVARCHAR/NCHAR field (due to historical table design reasons). If I was to make changes that dealt with integer fields (i.e through PK/FK relationship constraints) could this have much impact on performance?

Answer 1

I solved this problem by using a CHARINDEX function. I wanted to pass the string in as a single parameter. I created a string with leading and trailing commas for each value I wanted to test for. Then I concatenated a leading and trailing commas to the string I wanted to see if was "in" the parameter. At the end I checked for CHARINDEX > 0

DECLARE @CTSPST_Profit_Centers VARCHAR (256) 
SELECT @CTSPST_Profit_Centers = ',CS5000U37Y,CS5000U48B,CS5000V68A,CS5000V69A,CS500IV69A,CS5000V70S,CS5000V79B,CS500IV79B,'

SELECT 
   CASE
       WHEN CHARINDEX(','+ISMAT.PROFIT_CENTER+',' ,@CTSPST_Profit_Centers) > 0 THEN 'CTSPST'  
       ELSE ISMAT.DESIGN_ID  + ' 1 CPG' 
      END AS DESIGN_ID

You can also do it in the where clause

WHERE CHARINDEX(','+ISMAT.PROFIT_CENTER+',',@CTSPST_Profit_Centers) > 0

If you were trying to compare numbers you'd need to convert the number to a text string for the CHARINDEX function to work.

Answer 2

You can use a variable in an IN clause, but not in the way you're trying to do. For instance, you could do this:

declare @i int
declare @j int

select @i = 10, @j = 20

select * from YourTable where SomeColumn IN (@i, @j)

The key is that the variables cannot represent more than one value.

To answer your question, use the inline select. As long as you don't reference an outer value in the query (which could change the results on a per-row basis), the engine will not repeatedly select the same data from the table.

Answer 3

Based on your update and assuming the lookup table is small, I suggest trying something like the following:

DECLARE @MyLookup table
 (SomeValue nvarchar(100) not null)

SELECT
   case when ml.SomeValue is not null then 1 else 0 end AS Priority   
  ,t.column_b
  ,t.column_c
 from MyTable t
  left outer join @MyLookup ml
   on ml.SomeValue = t.column_a
 order by case when ml.SomeValue is not null then 1 else 0 end desc

(You can't reference the column alias "Priority" in the ORDER BY clause. Alternatively, you could use the ordinal position like so:

 order by 1 desc

but that's generally not recommended.)

As long as the lookup table is small , this really should run fairly quickly -- but your comment implies that it's a pretty big table, and that could slow down performance.

As for n[Var]char vs. int, yes, integers would be faster, if only because the CPU has fewer bytes to juggle around... which shoud only be a problem when processing a lot of rows, so it might be worth trying.