none
SQL ARRAY IN 'WHERE IN'

    Question

  • Dear All

    How could I declare a variable and then used in WHERE IN clause?

    I would like to do something like

    SELECT * FROM rooms WHERE number IN ('1001','1012','1015')

    but with the room number array declared as variable so I could reuse it

    I tried the following which is not working

    DECLARE @RoomNumbers VARCHAR(MAX) = '1001,1012,1015';

    SELECT * FROM rooms WHERE number IN (@RoomNumbers)

    If not using EXEC, is it possible to do it?  Thanks.

    Friday, August 29, 2014 3:17 AM

Answers

  • You can do it by creating a split function.  The are many examples of split functions, but one possibility is

    CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table
    AS
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces
      )
    GO

    Then you can use that split function, for example

    DECLARE @RoomNumbers VARCHAR(MAX) = '1001,1012,1015';
    
    SELECT * FROM rooms WHERE number IN (Select Element From dbo.Split(@RoomNumbers, ','))

    Tom

    • Marked as answer by swivan Friday, August 29, 2014 3:59 AM
    Friday, August 29, 2014 3:54 AM
  • >> How could I declare a variable and then used in WHERE IN clause? <<

    You decide that you do not care about data integrity, SQL injections and all that jazz. You will get plenty of kludges for this in a forum (splitters).

    Or you can decide to do it right: 

    https://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

    https://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/

    Have you ever read a book on RDBMS and the normal forms? It will keep you from making this conceptual error. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by swivan Friday, September 05, 2014 2:04 AM
    Friday, August 29, 2014 5:50 PM
  • Another way is this

    SELECT * FROM rooms WHERE ',' + @RoomNumbers + ',' LIKE '%,' + CAST(number AS varchar(15)) + ',%'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by swivan Friday, September 05, 2014 2:04 AM
    Friday, August 29, 2014 6:35 PM

All replies

  • You can do it by creating a split function.  The are many examples of split functions, but one possibility is

    CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table
    AS
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces
      )
    GO

    Then you can use that split function, for example

    DECLARE @RoomNumbers VARCHAR(MAX) = '1001,1012,1015';
    
    SELECT * FROM rooms WHERE number IN (Select Element From dbo.Split(@RoomNumbers, ','))

    Tom

    • Marked as answer by swivan Friday, August 29, 2014 3:59 AM
    Friday, August 29, 2014 3:54 AM
  • You can do it by creating a split function.  The are many examples of split functions, but one possibility is

    CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table
    AS
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces
      )
    GO

    Then you can use that split function, for example

    DECLARE @RoomNumbers VARCHAR(MAX) = '1001,1012,1015';
    
    SELECT * FROM rooms WHERE number IN (Select Element From dbo.Split(@RoomNumbers, ','))

    Tom


    Very good, Thanks
    Friday, August 29, 2014 3:59 AM
  • >> How could I declare a variable and then used in WHERE IN clause? <<

    You decide that you do not care about data integrity, SQL injections and all that jazz. You will get plenty of kludges for this in a forum (splitters).

    Or you can decide to do it right: 

    https://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

    https://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/

    Have you ever read a book on RDBMS and the normal forms? It will keep you from making this conceptual error. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by swivan Friday, September 05, 2014 2:04 AM
    Friday, August 29, 2014 5:50 PM
  • Another way is this

    SELECT * FROM rooms WHERE ',' + @RoomNumbers + ',' LIKE '%,' + CAST(number AS varchar(15)) + ',%'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by swivan Friday, September 05, 2014 2:04 AM
    Friday, August 29, 2014 6:35 PM