SQL Server : issue with stored procedure with multiple result sets

Question

I'm trying to get multiple result set from a stored procedure in EF5. I'm trying to cut the number of database round-trips for the most expensive functions in our platform.

We have a view that updates through server sent events. When the view data changes every user needs to receive their custom view with the updated data. Our current approach is getting all connected users and then get their view from the database per user. Because this is very expensive we were trying to make it a little bit cheaper by implementing one call to a stored procedure returning multiple results.

I have the following code:

var sql = "DECLARE @users UserList INSERT INTO @users(row) VALUES {0}" +
                  "EXEC dbo.GetTracklistViews(@users, '{1}');";
var sql_shard = "('{0}'),";
var sql_shard_last = "('{0}')";

var users = new List<Guid>();

//Logic to get all connected users
var builder = new StringBuilder();

for (var i = 0; i < users.Count; i++)
    if (i < (users.Count - 1))
       builder.Append(string.Format(sql_shard, users[i]));
    else
       builder.Append(string.Format(sql_shard_last, users[i]));

using (var ctx = new spottyData())
{
    ctx.Database.Initialize(force: false);
    ctx.Database.Connection.Open();

    using (var cmd = ctx.Database.Connection.CreateCommand())
    {
        cmd.CommandText = string.Format(sql, builder.ToString(), juke.ToString());

        var result = cmd.ExecuteReader();

        while (result.NextResult())
        {
            var set = ((IObjectContextAdapter)ctx)
                        .ObjectContext
                        .Translate<tracklist>(result, "tracklist", System.Data.Objects.MergeOption.NoTracking)
                        .ToArray();

            //Send event with 'set' data
        }
    }
}

On var result = cmd.ExecuteReader(); it throws an SqlException:

Incorrect syntax near '@users'.

We've used the same syntax to input multiple values in several other stored procedures and functions before. Everything seems to be fine. I think it has something to do with EXEC dbo.GetTracklistViews....

Also if any of you guys think we're trying to achieve something completely crazy here, is there any other way we can achieve this? Or do we have to go look for other ways to cut expensive functions?

The definition of the UserList type:

CREATE TYPE UserList
AS TABLE (
    [row] INT IDENTITY(1,1) PRIMARY KEY,
    [user_id] UNIQUEIDENTIFIER
);

Answer 1

Firstly - you shouldn't be using a comma separated list of values in a string as a parameter. Its pretty inefficient for SQL server to parse and split that. Instead your stored procedure should use a Table valued parameter.

Secondly - try your hardest to avoid multiple result sets. Its very messy. Since all your results sets are tables of the same type I'd just create a new class (call it UserTrackList for example) - with the same properties as Tracklist + 1 more for the UserId. It could inherit from TrackList even

For example:

The table valued type to pass in:

CREATE TYPE GuidList
AS TABLE (
    [id] UNIQUEIDENTIFIER -- < assume thats what you're actually passing in?
);

The Stored procedure definition

CREATE PROCEDURE dbo.GetTracklistViews
    @users GuidList READONLY,
    @juke NVARCHAR(1000) -- < I have no idea what this parameter is!
AS
BEGIN

-- SELECT a load of stuff - joined onto this table valued parameter?

END 

How to call the procedure in C#

var sql = "EXEC dbo.GetTrackListViews @users, @juke";
var prms = new List<System.Data.SqlClient.SqlParameter>();

//build table valued parameter
var tbl = new DataTable();
tbl.Columns.Add(new DataColumn("id", typeof(guid));
foreach(var user in users) //assume these are guids.
{ tbl.Rows.Add(user) }
var prm1 = new System.Data.SqlClient.SqlParameter("users", SqlDbType.Structured);
prm1.Value = tbl;
prm1.TypeName = "GuidList";

prms.Add(prm1);

// other parameter is easy:
var prm2 = new System.Data.SqlClient.SqlParameter("juke", juke.ToString());
prms.Add(prm2);

List<UserTrackList> results;
using (var ctx = new spottyData())
{
    var query = ctx.Database.SqlQuery<UserTrackList>(sql, prms.ToArray());
    results = query.ToList();
    // NB - this works if the property names/types in the type "tracklist" 
    // match the column names/types in the returned resultset.
}

// example processing the results
foreach (var tracklist in results.GroupBy(x => x.UserId)
{
   user = users.First(x => x == tracklist.Key);
   //do something with the grouped result
}