How to call a stored procedure from MVC that requires a list of ids

Question

I got a good answer to my previous question but it's actually even more complicated:

I need to pass to this stored procedure a list of ints:

ALTER proc [dbo].[Invoice_GetHomePageInvoices] (
      @AreaIdList varchar(max)
,      @FinancialYearStartDate datetime = null
,      @FinancialYearEndDate datetime = null

Here's what I have so far:

public virtual IEnumerable<HomePageInvoice> GetHomePageInvoices(IList<Area> areas, FinancialYearLookup financialYear)
{
    var areaIds = areas.Select(x => x.Id).ToList();

    //return _db.Database.SqlQuery<HomePageInvoice>(string.Format("EXEC Invoice_GetHomePageInvoices @AreaIdList = '{0}', @FinancialYearStartDate = '{1}', @FinancialYearEndDate = '{2}'", areas.ToString(), financialYear.StartDate.ToString(), financialYear.EndDate.ToString()));

    var startDate = new SqlParameter("FinancialYearStartDate", financialYear.StartDate);
    var endDate = new SqlParameter("FinancialYearEndDate", financialYear.EndDate);

    return _db.Database.SqlQuery<HomePageInvoice>("Invoice_GetHomePageInvoices", startDate, endDate);
}

So the the datetime parameters are sorted. But how would you send the ids to the stored proc given I current have a List<int> and sql expects @AreaIdList varchar(max)

score 2 · Accepted Answer · answered Apr 16 '12 at 22:32

2

String.Join?

String.Join(",", areaIds)

answered Apr 16 '12 at 22:32

canon

40,609
10
73
97

For a more elegant solution, see my previous answer here: http://stackoverflow.com/a/7487909/621962 – canon Apr 16 '12 at 23:48

score 1 · Answer 2 · answered Apr 16 '12 at 22:53

1

Table-valued parameters, you should really read the post below. He does a fantastic job at explaining how it all should work

http://www.sommarskog.se/arrays-in-sql-2008.html

answered Apr 16 '12 at 22:53

coffeeyesplease

1,018
9
15

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

Convert your List<int> to a comma-separated string, and pass that to your stored procedure.

Converting a generic list to a CSV string

edited May 23 '17 at 10:34

Community

1
1

answered Apr 16 '12 at 22:35

Robert Harvey

178,213
47
333
501

score 0 · Answer 4 · answered Apr 16 '12 at 22:38

I really depends on what you are doing with that list. But if you are using it for an IN clause or something like that then I would build that out in C# and pass it to the proc:

StringBuilder s = new StringBuilder();
foreach (var i in areaIds)
{
    s.Append(String.Format("'{0}',", i));
}
s.TrimEnd(',');
var areas = new SqlParameter("AreaIdList", s.ToString());

Then in the proc you can use it like:

WHERE area IN @AreaIdList

How to call a stored procedure from MVC that requires a list of ids

4 Answers4