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

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:
  1. SELECT *  
  2. FROM Products  
  3. 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.
  1. CREATE TYPE Type_ApiProductIDs AS TABLE    
  2. (  
  3.     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.
  1. CREATE PROCEDURE spGetAllProductsByIDs  
  2. (  
  3.     @ProductsTVP Type_ApiProductIDs READONLY  
  4. )  
  5. AS  
  6. BEGIN  
  7.     SELECT  
  8.         ID,  
  9.         ProductName,  
  10.         ProductPrice,  
  11.         ProductDescription  
  12.     FROM   
  13.         CMS_Products p  
  14.     INNER JOIN @ProductsTVP AS tvp ON p.ID = tvp.ProductID   
  15. 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.  
  1. public static void GetProductsBySearchTerm(string searchTerm)  
  2. {  
  3.     #region Get Product IDs from API Based On Search Term  
  4.   
  5.     List<int> searchProductIds = SearchApiHelper.GetResults(searchTerm); // Get list of product ID's.  
  6.   
  7.     // Create a Data Table in the same structure to User Data-Type.  
  8.     DataTable prodIdsTable = new DataTable();  
  9.     prodIdsTable.Columns.Add(new DataColumn("ProductID", Type.GetType("System.Int32")));  
  10.   
  11.     // Populate "prodIdsTable" datatable with ID's from SearchApiHelper.  
  12.     if (searchProductIds?.Count > 0)  
  13.     {  
  14.         foreach (int id in searchProductIds)  
  15.         {  
  16.             DataRow newRow = prodIdsTable.NewRow();  
  17.             newRow["ProductID"] = id;  
  18.             prodIdsTable.Rows.Add(newRow);  
  19.         }  
  20.     }  
  21.  
  22.     #endregion  
  23.   
  24.     if (prodIdsTable?.Rows.Count > 0)  
  25.     {  
  26.         DataSet dsProducts = new DataSet();  
  27.   
  28.         using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))  
  29.         {  
  30.             try  
  31.             {  
  32.                 sqlConn.Open();  
  33.   
  34.                 using (SqlCommand sqlCmd = new SqlCommand("spGetAllProductsByIDs", sqlConn))  
  35.                 {  
  36.                     sqlCmd.CommandType = CommandType.StoredProcedure;  
  37.   
  38.                     SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ProductsTVP", prodIdsTable);  
  39.                     tvpParam.SqlDbType = SqlDbType.Structured;  
  40.   
  41.                     SqlDataAdapter da = new SqlDataAdapter();  
  42.                     da.SelectCommand = sqlCmd;  
  43.                     da.Fill(dsProducts);  
  44.                 }  
  45.   
  46.                 // Do something with the data returned from dsProducts DataTable...  
  47.             }  
  48.             catch (Exception e)  
  49.             {  
  50.                 throw e;  
  51.             }  
  52.         }  
  53.     }  

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