Alternative To SQL "In" Condition When Working With Many Values

In this post, we will be discussing an alternative to using a SQL “IN” condition, where many values need to be parsed to the SQL query through the use of a User Defined Data Type to get exact records back based on a list of ID's.

Introduction

In this post, we will be discussing an alternative to using a SQL “IN” condition, where many values need to be parsed to the SQL query through the use of a User Defined Data Type to get exact records back based on a list of ID's.

 

There will probably be a very small number of cases where additional steps (as detailed in this post) will need to be carried out. Afterall, SQL Server has a very large limit on the number of values the “IN” condition can handle, based on the length of instruction (max 65k).

 

However, based on personal experience where there is a bigger need for dealing with large volumes of data, investing extra time in database architecture is always good.

The “IN” Condition

For those who are not familiar, the “IN" condition is probably one of the most flexible methods of returning specific records from a table based on multiple list of values. The “IN” condition is a nice shorthand way to carry out multiple OR conditions. A basic example of the condition in use is:

SELECT *
FROM Products
WHERE ID IN (1, 2, 3, 4)

User Defined Data Type

First introduced in SQL Server 2008, User Defined Data Types defines a way to storing temporary data in a structure similar to a table. The User Defined Data Type can be populated through a Table-Valued parameter within a stored procedure. This will be the key to what we will be using in dealing with many values for our alternative to using the IN condition.

Scenario: Selecting List of Products

I encountered a scenario that required a flexible way to get back a number of specific records from my database when I received a list of product ID’s from an external API for cross-referencing. Normally, I would just grab all the ID’s and parse them directly into the IN condition. It was an unknown as to how many ID’s would received from the external API and a different approach needed to be considered to ensure the application would be scalable.

Create A New User Defined Data Type

The first thing we need to do is to create a User Defined Data Type that will allow us to pass the list of our product ID’s in a table structure. The structure itself is very simple and contains just one column called ProductID.

CREATE TYPE Type_ApiProductIDs AS TABLE
(
    ProductID int
)

Create Stored Procedure

Next we create a stored procedure called “spGetAllProductsByIDs” that will contain a parameter based on the data type we created above. In this case, the parameter is @ProductsTVP. Note that when using a variable based on a data type in a stored procedure, it must be declared as READONLY.

CREATE PROCEDURE spGetAllProductsByIDs
(
    @ProductsTVP Type_ApiProductIDs READONLY
)
AS
BEGIN
    SELECT
        ID,
        ProductName,
        ProductPrice,
        ProductDescription
    FROM
        CMS_Products p
    INNER JOIN @ProductsTVP AS tvp ON p.ID = tvp.ProductID
END

The stored procedure is simply getting back all products from CMS_Products table based on a values stored in @ProductsTVP parameter through a join. Now all that needs to be done is to use this stored procedure in code.

Passing User Defined Data Type In Code

A method called GetProductsBySearchTerm() gets a list of product ID’s based on search terms from a call to the SearchApiHelper class, which then transforms this output to the “prodIdsTable" DataTable structure similar to our Type_ApiProductIDs data type. To use this DataTable with our stored procedure, we’ll be using a SqlDbType.Structured SQL parameter.

 

In addition to a DataTable, a IEnumerable<SqlDataRecord> and DbDataReader can be used in conjunction with the SqlDbType.Structured parameter.  

public static void GetProductsBySearchTerm(string searchTerm)
{
    #region Get Product IDs from API Based On Search Term

    List<int> searchProductIds = SearchApiHelper.GetResults(searchTerm); // Get list of product ID's.

    // Create a Data Table in the same structure to User Data-Type.
    DataTable prodIdsTable = new DataTable();
    prodIdsTable.Columns.Add(new DataColumn("ProductID", Type.GetType("System.Int32")));

    // Populate "prodIdsTable" datatable with ID's from SearchApiHelper.
    if (searchProductIds?.Count > 0)
    {
        foreach (int id in searchProductIds)
        {
            DataRow newRow = prodIdsTable.NewRow();
            newRow["ProductID"] = id;
            prodIdsTable.Rows.Add(newRow);
        }
    }

    #endregion

    if (prodIdsTable?.Rows.Count > 0)
    {
        DataSet dsProducts = new DataSet();

        using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
        {
            try
            {
                sqlConn.Open();

                using (SqlCommand sqlCmd = new SqlCommand("spGetAllProductsByIDs", sqlConn))
                {
                    sqlCmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ProductsTVP", prodIdsTable);
                    tvpParam.SqlDbType = SqlDbType.Structured;

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = sqlCmd;
                    da.Fill(dsProducts);
                }

                // Do something with the data returned from dsProducts DataTable...
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}

For more useful information about User Defined Data Types, please read a great C# Corner article by Pankaj Kumar Choudhary,

• User Defined Table Types And Table Valued Parameters