SQL Select rows with multiple integer values

Question

My table contains an integer value. I want to present multiple values sort of like:

select 
    MeetingId, StartDate, EndDate, RoomId, MeetingStatusId, Subject
from 
    Meeting
where 
    RoomId in (@roomids )
    and StartDate >= @start and EndDate <= @end
    and CreatedById = @user

But how do I construct the @roomids parameter in C# to be integers? I tried casting RoomId to a varchar, but that didn't work.

Answer 1

You can use SQL Server STRING_SPLIT function and use the parameter as varchar:

select 
    MeetingId, StartDate, EndDate, RoomId, MeetingStatusId, Subject
from 
    Meeting
where 
    RoomId in (SELECT cast(VALUE as int) FROM dbo.string_split(@roomids) )
    and StartDate >= @start and EndDate <= @end
    and CreatedById = @user

Reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

If you don't have the build in split string function due to your SQL Server version, here's an article on how to create one: https://sqlperformance.com/2012/07/t-sql-queries/split-strings

Answer 2

A table-valued parameter is a good choice here. For starters you'd create a custom table type in SQL Server, like so:

create type dbo.IdentifierList as table (Identifier int not null);

Here's a simple C# function to create an instance of a query parameter having this type:

SqlParameter CreateIdentifierTableParameter(string name, IEnumerable<int> identifiers)
{
    // Build a DataTable whose schema matches that of our custom table type.
    var identifierTable = new DataTable(name);
    identifierTable.Columns.Add("Identifier", typeof(long));
    foreach (var identifier in identifiers)
        identifierTable.Rows.Add(identifier);

    return new SqlParameter
    {
        ParameterName = name,              // The name of the parameter in the query to be run.
        TypeName = "dbo.IdentifierList",   // The name of our table type.
        SqlDbType = SqlDbType.Structured,  // Indicates a table-valued parameter.
        Value = identifierTable,           // The table created above.
    };
}

Then you write your query with the @RoomIds parameter treated as if it were any other table in your database, call the function created above to build the table-valued parameter, and then add it to your SQL command just like you would any other SqlParameter. For instance:

void GetMeetings(IEnumerable<int> roomIdentifiers)
{
    // A simplified version of your query to show just the relevant part:
    const string sqlText = @"
        select
            M.*
        from
            Meeting M
        where 
            exists (select 1 from @RoomIds R where M.RoomId = R.Identifier);";

    using (var sqlCon = new SqlConnection("<your connection string here>"))
    {
        sqlCon.Open();
        using (var sqlCmd = new SqlCommand(sqlText, sqlCon))
        {
            sqlCmd.Parameters.Add(CreateIdentifierTableParameter("RoomIds", roomIdentifiers));

            // Execute sqlCmd here in whatever way is appropriate.
        }
    }
}

This seems like a lot of work at first, but once you've defined the SQL type and written some code to create instances of it, it's really easy to re-use wherever you need it.

Answer 3

Try This

        string RooomList = "5,3,7";
        string DateS = "01-01-2017";
        string DateE = "12-31-2017";
        string userT = "Rami";
        string sqlText = string.Format(@"
        select  MeetingId, StartDate, EndDate, RoomId, MeetingStatusId, Subject
        from Meeting 
        where 
        RoomId in ({0} )
        and StartDate >= {1}  and EndDate <= {2} 
         and CreatedById = {3} ", RooomList, DateS , DateE , userT);