T-SQL - Integer Array - Condition based
Hack for SQL Server 2005 limitations
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.