How can I declare an array in MSSQL to use it in a WHERE condition?

In MSSQL, you cannot directly declare an array like you would in some programming languages. However, you can achieve similar functionality using several methods to use a list of values in a WHERE condition. Here are the common approaches:

1. Using the `IN` Operator:

- The most straightforward way to use a list of values in a `WHERE` clause is with the `IN` operator. This allows you to specify a list of values that a column should match.

- Example:

SELECT
FROM YourTable
WHERE YourColumn IN (Value1, Value2, Value3);

- In this example, `YourColumn` will be checked against `Value1`, `Value2`, and `Value3`. If it matches any of them, the row will be included in the result set.

2. Using a Table-Valued Parameter (TVP):

- For more complex scenarios, especially when dealing with a large number of values or when the list is dynamic, Table-Valued Parameters are a powerful option. This involves creating a custom table type and passing it as a parameter to a stored procedure or function.

- Example:

-- 1. Create a Table Type
CREATE TYPE IntList AS TABLE (
  IntValue INT
);

-- 2. Create a Stored Procedure
CREATE PROCEDURE GetRowsByIntList
  @IntValues IntList READONLY
AS
BEGIN
  SELECT
  FROM YourTable
  WHERE YourColumn IN (SELECT IntValue FROM @IntValues);
END;

-- 3. Execute the Stored Procedure
DECLARE @MyIntList IntList;
INSERT INTO @MyIntList (IntValue) VALUES (1), (2), (3);
EXEC GetRowsByIntList @IntValues = @MyIntList;

- This approach is more structured and efficient for larger lists and can be used in stored procedures or functions.

3. Using a Comma-Separated String and String Splitting:

- Another method involves passing a comma-separated string and then splitting it into individual values. This is less efficient than TVPs but can be useful in simpler cases.

- Example:

-- Function to split a comma-separated string
CREATE FUNCTION dbo.SplitString (@String VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Results TABLE (Value VARCHAR(MAX))
AS
BEGIN
  DECLARE @pos INT, @start INT;
  SET @start = 1;
  WHILE (CHARINDEX(@Delimiter, @String, @start) > 0)
  BEGIN
    SET @pos = CHARINDEX(@Delimiter, @String, @start);
    INSERT INTO @Results (Value) VALUES (SUBSTRING(@String, @start, @pos - @start));
    SET @start = @pos + 1;
  END;
  INSERT INTO @Results (Value) VALUES (SUBSTRING(@String, @start, LEN(@String)));
  RETURN;
END;

-- Using the function in a query
DECLARE @MyString VARCHAR(MAX) = 'Value1,Value2,Value3';
SELECT
FROM YourTable
WHERE YourColumn IN (SELECT Value FROM dbo.SplitString(@MyString, ','));

- This method requires a string splitting function, which can be custom-built or found online.

In summary, while MSSQL doesn't have a direct array declaration, you can effectively use the `IN` operator, Table-Valued Parameters, or string splitting techniques to handle lists of values in your `WHERE` conditions. The choice of method depends on the complexity and scale of your requirements.

Learn more about this