T-SQL - Integer Array - Condition based

Yesterday, I was presented with a simple problem by a colleague to be completed in SQL Server 2005 Transact SQL.

We need to write a complex SELECT statement which will take in some parameters conditionally. The parameters were integers.

So we got down to finding a solution and this is what we came with for SQL Server 2005. This is not the actual query, but a more simplified one for ease of reading.

CREATE PROCEDURE getNodes
  @isActive bit
AS
  BEGIN
    -- Declare a single column table to be used as a single dimension array
    DECLARE @activeNodes TABLE (id int)

    -- Only if the following condition is satisfied, 1070 will be added to the pseudo-array
    IF @isActive = 1
      INSERT INTO @activeNodes(id) VALUES (1070)

    -- 1067 and 1069 will be anyhow added to the pseudo-array
    INSERT INTO @activeNodes(id) VALUES (1067)
    INSERT INTO @activeNodes(id) VALUES (1069)

    -- Now an IN clause could be used, really useful in more complex queries
    SELECT [nodeId],[xml]
      FROM [cmsContentXml]
      WHERE [nodeId] IN (SELECT id FROM @activeNodes)

  END

GO

For SQL Server 2008 and above, a single statement can be used to add multiple values to our pseudo-array.

ALTER PROCEDURE getNodes
  @isActive bit
AS
  BEGIN
    -- Declare a single column table to be used as a single dimension array
    DECLARE @activeNodes TABLE (id int)

    -- Only if the following condition is satisfied, 1070 will be added to the pseudo-array
    IF @isActive = 1
      INSERT INTO @activeNodes(id) VALUES (1070)

    -- 1067 and 1069 will be anyhow added in one statement (SQL 2008 and higher) to the pseudo-array
    INSERT INTO @activeNodes(id) VALUES (1067), (1069)    

    -- Now an IN clause could be used, really useful in more complex queries
    SELECT [nodeId], [xml]
          FROM [cmsContentXml]
          WHERE [nodeId] IN (SELECT id FROM @activeNodes)

  END

GO

This Stackoverflow question was a great help to put us on the right path. My two cents worth is knowing how to do it SQL Server 2005.

---

This article was originally written on Google's Blogger platform and ported to Hashnode on 17 Sep 2022.