T-SQL - Integer Array - Condition based

Hack for SQL Server 2005 limitations

Emmanuel Tissera's photo
Emmanuel Tissera
·Jul 3, 2013·

2 min read

T-SQL - Integer Array - Condition based

Photo by Rubaitul Azad on Unsplash

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.

 
Share this