Use of WHERE IN with a list in SQL 2008 R2

Transact-SQL
1

Having trouble getting the following to work. I have a known list of IDs, where I would like to select details from the database. I am using the following resulting incorrect syntax near ',' Any thoughts?

DECLARE @List int
Set @List = 710800168,710813843,710799889,710745846,710839725,710821798,710836139,710829653,710846897,710789736,710840949,810813894,710833293,710825216,810818478,710721686,710722817,510733823,710840949,710824166,710726615

SELECT TOP (1000) [ia_Record_Date]
,[DISPLAYNAME]
,[EMPLOYEENUMBER]
,[MHCSEGMENT]
,[COUNTRY]
,[EMPLOYEETYPE]
,[MHCHRLOCATION]
,[NSACCOUNTLOCK]

FROM [Corporate].[dbo].[LDAP] where EMPLOYEENUMBER = (@List)

2

An "int" data type can only store a single integer value. You'd have to split a list of values to use IN () as well.

3

Just to save you some time, you cannot use the "EMPLOYEENUMBER in (@Lists)" construct either; even if lists is a comma separate string with your values listed in it. What you can do is put your values into a (temp?) table and then use the IN operator against that table (e.g., EMPLOYEENUMBER in (select value from #MyIntegers) ).

4

You could use dynamic SQL:

DECLARE @List varchar(2000)='710800168,710813843,710799889,710745846,710839725,710821798,710836139,710829653,710846897,710789736,710840949,810813894,710833293,710825216,810818478,710721686,710722817,510733823,710840949,710824166,710726615'
DECLARE @sql varchar(2000) = 'SELECT TOP (1000) [ia_Record_Date]
,[DISPLAYNAME]
,[EMPLOYEENUMBER]
,[MHCSEGMENT]
,[COUNTRY] 
,[EMPLOYEETYPE]
,[MHCHRLOCATION]
,[NSACCOUNTLOCK]
FROM [Corporate].[dbo].[LDAP] where EMPLOYEENUMBER IN ('+@List+')';
EXEC(@sql);
5

Nothing wrong with that route, but before you go too far down in please read up on "SQL Injection" which has potential nasty side effects when executing dynamic SQL

6

Jotorre, I like your code, Thanks. When I run it I get the following

error, Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'N00531602' to data type int.

7

Well ... that's not implicitly convertable to an INTEGER

8

Any ideas how to move past this error? I am new to SQL about 6 months.

9

If EMPLOYEENUMBER requires a number you can't match it to "N00531602". Make sure your @List only contains valid numbers

10

It looks like the column EMPLOYEENUMBER is not defined as a numeric data type (integer) - but your list is built out expecting integer values.

Modify your list so they are looking for string values...

Declare @List varchar(max);
    Set @List = '''710800168'',''710813843''';
    
    Select @List

You need to enclose each item in the list in single-quotes...

Since you are hard-coding this I would modify it to use a table variable...

Declare @listTable Table (EmployeeNumber varchar(50));

 Insert Into @listTable (EmployeeNumber)
 Values ('710799889')
      , ('710745846')
      , ('710839725')
      , ('710821798')
      , ('710836139')
      , ('710829653')
      , ('710846897')
      , ('710789736')
      , ('710840949')
      , ('810813894')
      , ('710833293')
      , ('710825216')
      , ('810818478')
      , ('710721686')
      , ('710722817')
      , ('510733823')
      , ('710840949')
      , ('710824166')
      , ('710726615');

 Select *
   From @listTable;

 Select Top(1000) [ia_Record_Date]
      , [DISPLAYNAME]
      , [EMPLOYEENUMBER]
      , [MHCSEGMENT]
      , [COUNTRY]
      , [EMPLOYEETYPE]
      , [MHCHRLOCATION]
      , [NSACCOUNTLOCK]
   From [Corporate].[dbo].[LDAP]
  Where EMPLOYEENUMBER In (Select EmployeeNumber e From @listTable e)

If you are looking to create a procedure and pass in the values - then you really should consider splitting the values passed into the procedure using a string split function.

11

Ah, that might well be it. I had assumed that the values in @List had changed (from the example) to include the 'N00531602' value.

All sorts of ways to check that the [EMPLOYEENUMBER] column is numeric, probably using SSMS is easiest, but if working in a SQL tool then:

EXEC [Corporate].[dbo].sp_help 'LDAP'

will list all the columns, in that table, and their DataType

Might also be worth looking at using a Splitter Function on the "list" and then JOINing to the results of that (or using IN [on the Splitter Function's results] as per @jeffw8713 example)