How to query the database with a list of key-value pairs

database, sql, sql-server

Solution

You can create a temp table

create table #temp (Category varchar(50), Name varchar(50))
insert into #temp values ('Cat1', 'abc'), ('Cat2', 'cde'), ('Cat3', 'eee')

And then join your main table

select * from table1
inner join #temp
on table1.Category = #temp.Category and table1.Name = #temp.Name

If you want to use that approach from the code, you can do that using table parameters.

Define a table type:

CREATE TYPE dbo.ParamTable AS TABLE
    ( Category varchar(50), Name varchar(50) )

and a stored proc that will read the data:

create procedure GetData(@param dbo.ParamTable READONLY)
AS
    select * from table1
    inner join @param p
    on table1.Category = p.Category and table1.Name = p.Name

Then you can use those from the C# code, for example:

using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=Test2;Integrated Security=True"))
{
    conn.Open();

    DataTable param = new DataTable();
    param.Columns.Add(new DataColumn("Category", Type.GetType("System.String")));
    param.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
    param.Rows.Add(new object[] { "Cat1", "abc" });

    using (var command = conn.CreateCommand())
    {
        command.CommandText = "GetData";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@param", param);

        using (var reader = command.ExecuteReader())
        {
            // reading here
        }
    }

}

Problem

Say I have the a table with 3 columns : Id, Category, Name. I would like to query the table that way : Get me the rows for which `{ Category = "Cat1" AND Name = "ABC" }` OR `{ Category = "Cat2" AND Name = "ABC" }` OR `{ Category = "Cat2" AND Name = "DEF" }` How? Without having to resort to a huge list of `WHERE OR` I was thinking of using `IN`...but is it possible to use that in conjunction with 2 columns? Thanks!

Original source