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:

Make '@AvailableValues' a comma-delimited string and use a LIKE statement. This does not perform well.
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.
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.
???

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?

Take a look at Erland Sommarskog's article here: Arrays and Lists in SQL Server 2005 and Beyond For 2000 and 2008 versions, look here: sommarskog.se/arrays-in-sql.html — SQLMenace
– SQLMenace, Commented Mar 21, 2011 at 18:13
Gotta be more going on here than SELECT count(*) from MyTable where MyColumn in (select LookupValue from LookupTable). Post the full query and someone will help optimize it. — Philip Kelley
– Philip Kelley, Commented Mar 21, 2011 at 18:14
@asawyer - already using table variables in the SP, had performance problems here. — Zac Seth
– Zac Seth, Commented Mar 21, 2011 at 18:34
@Philip Kelly, I've updated the examples to reflect what I'm trying to do better. — Zac Seth
– Zac Seth, Commented Mar 21, 2011 at 18:35

Adam Robinson · Accepted Answer · 2011-03-21 18:14:10Z

9

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.

answered Mar 21, 2011 at 18:14

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zac Seth Over a year ago

Yeah, well, I tried using an inline-select based upon a table variable but the performance was terrible. In the past I've always used table variables to good effect (in terms-of performance) but it isn't working this time. Cheers for the answer though.

Zac Seth Over a year ago

The solution that we eventually came to incorporated the example code you provided (alongside a some other changes we made to column datatypes). It isn't the most flexible/maintainable solution but, because we're only using a small number of variables in the IN clause and this isn't likely to change, we managed to get the performance we were looking for with this approach.

Philip Kelley · Accepted Answer · 2011-03-23 14:07:52Z

2

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.

answered Mar 23, 2011 at 14:07

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

2 Comments

Zac Seth Over a year ago

Actually, using a LEFT join was the first technique I tried however we had performance issues here too. After spending time on the solution it has become apparent that we need to do some optimisation on our indexes - if the tables were indexed better I think this solution would have been perfect. As a side note, you can reference 'Priority' in the ORDER BY (I was able to). Cheers, Zac

Philip Kelley Over a year ago

Your right. It's true for GROUP BY, which is what always messes me up.

bummi · Accepted Answer · 2016-01-12 00:06:09Z

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.

bic · Accepted Answer · 2011-03-21 19:23:26Z

0

This might be along the lines of what you need.
Note that this assumes that you have permissions and the input data has been sanitized.

From Running Dynamic Stored Procedures

CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)

    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM TableName WHERE " + @WHEREClause

    -- Execute the SQL statement
    EXEC(@SQLStatement)

edited Mar 21, 2011 at 19:23

answered Mar 21, 2011 at 18:45

bic

2,28126 silver badges28 bronze badges

5 Comments

Zac Seth Over a year ago

My understanding is that this would lose performance because there would be no execution plan. Can anyone confirm/deny/improve upon my understanding here? Cheers.

bic Over a year ago

Im not sure about that to be perfectly honest. [Anecdotal evidence :-) - I've used it before in certain conditions with literally millions of rows and haven't noticed any parictular performance problems.] It should be pretty easy to test though from your described scenario.

bic Over a year ago

4guysfromrolla.com/webtech/sqlguru/q120899-2.shtml - "Beginning with SQL 7.0, there is a handy little proc called sp_executeSQL that will accept parameterized queries. It will also attempt to re-use query plans, so if you execute the same statement multiple times, chances are you'll get a reused query plan." apparently you will only lose parse and compile time performance.

Philip Kelley Over a year ago

Frequent recompilations would cause performance to suffer, but the extra time involved might be minute (+12 ms, or the like) -- test and see, it depends on how complex the query is. And try sp_executeSQL, again depending on query complexity it may allow for better performance.

Zac Seth Over a year ago

We ended up with another solution but your answers are good to know.

Collectives™ on Stack Overflow

TSQL Variable With List of Values for IN Clause

4 Answers 4

2 Comments

2 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related