# T-SQL - Integer Array - Condition based

### 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](https://stackoverflow.com/questions/13508617/int-array-and-in-statement) 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.*