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.

Instead of giving a list of room IDs in your `WHERE` clause in order to filter your results, you can just use an `INNER JOIN` to a table or common table expression to restrict your results. So instead of specifying your room ids inline in your query, make a temp table or a common table expression which contains the room ids you want to filter by and then join to that. — EMUEVIL, Jul 20 '18 at 15:43
You could create a user-defined table type, then use this in C# (either as a data table or some other mechanism) to pass multiple values. Back in SQL you would join to the parameter as if it were a table. — Richard Hansell, Jul 20 '18 at 15:46
You could use dapper to pass the parameters - here's how to [use it with an IN clause](https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm) — gbjbaanb, Jul 20 '18 at 15:47

Ctznkane525 · Answer 1 · 2018-07-20T15:57:32.227

3

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

edited Jul 20 '18 at 15:57

answered Jul 20 '18 at 15:44

Ctznkane525

7,297
3
16
40

Correct, but this assumes SQL Server 2016 and they asked how to construct the parameter in c# – S3S Jul 20 '18 at 15:44
earlier versions support it (2014 at least)...and it requires changes in sql server to do it...they would construct it as a varchar string...i mentioned that – Ctznkane525 Jul 20 '18 at 15:45
can you elaborate on what earlier versions support it and what changes are required in sql server to do it? I'm unaware of this. Thanks! – S3S Jul 20 '18 at 15:46
This is a hand written split string, not the one built into later versions of SQL Server... but it's still not a very good solution. – Richard Hansell Jul 20 '18 at 15:48
@scsimon I don't think it matter the version Because if it doesn't exist like in SQL 2008 (which is what i have). You can simply create it yourself which i have already done in the past – Franck Jul 20 '18 at 15:48
i was about to mention that...its easy to add a split string udf...assuming you dont have it ootb – Ctznkane525 Jul 20 '18 at 15:49
@Franck i have many custom splitters, but STRING_SPLIT is build in, but only 2016 onwards that i'm aware. If this is a custom splitter, then the code for that function should be included. Mainly because there are a ton of HORRIBLE splitters on the net that use loops.... – S3S Jul 20 '18 at 15:49
the parameter value for the proc would be a string...e.g. "2,4,8" to get 3 rooms with those IDs – Ctznkane525 Jul 20 '18 at 15:50
@scsimon Yes, i don't deny that. I was going to mention that it would be great to add the custom function to the answer in case someone doesn't have the built in one. – Franck Jul 20 '18 at 15:53
I understand how the *function* (not proc) parameters would be passed in @Ctznkane525 and i assume you were using the built in function since, you linked the Microsoft article to it. So, again, 2016 onward. – S3S Jul 20 '18 at 15:54
i added a reference to an article on creating one if needed – Ctznkane525 Jul 20 '18 at 15:57
@Ctznkane525 the new link is exactly the one i use. I use the XML one. I can vouch it works in SQL 2008. – Franck Jul 20 '18 at 16:00

score 0 · Answer 2 · answered Jul 20 '18 at 16:31

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.

score 0 · Answer 3 · answered Jul 23 '18 at 08:52

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);

SQL Select rows with multiple integer values

3 Answers3