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.
For SQL Server 2008 and above, a single statement can be used to add multiple values to our pseudo-array.
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.