Accessing dynamically created stored procedure fro

Question

I'm pivoting data in MS SQL stored procedure. Columns which are pivoted are dynamically created using stored procedure parameter (for exampe: "location1,location2,location3,") so number of columns which will be generated is not known. Output should look like (where locations are taken from stored procedure parameter):

OrderTime | Location1 | Location2 | Location3

Any chance that this can be used in LINQ to SQL? When I dragged this procedure to dbml file it shows that this procedure returns int type.

Columns I use from log_sales table are:

• Location (various location which I'm pivoting),
• Charge (amount of money)
• OrderTime

Stored procedure:

CREATE PROCEDURE [dbo].[proc_StatsDay] @columns NVARCHAR(64) AS

DECLARE @SQL_PVT1 NVARCHAR(512), @SQL_PVT2 NVARCHAR(512), @SQL_FULL NVARCHAR(4000);

SET @SQL_PVT1 =  'SELECT OrderTime, ' + LEFT(@columns,LEN(@columns)-1) +'
FROM (SELECT ES.Location, CONVERT(varchar(10), ES.OrderTime, 120),ES.Charge
        FROM dbo.log_sales ES
        ) AS D (Location,OrderTime,Charge)
        PIVOT (SUM (D.Charge) FOR D.Location IN
            (';
SET @SQL_PVT2 = ') )AS PVT
ORDER BY OrderTime DESC';

SET @SQL_FULL = @SQL_PVT1 + LEFT(@columns,LEN(@columns)-1) + 
@SQL_PVT2;       

EXEC sp_executesql @SQL_FULL, N'@columns NVARCHAR(64)',@columns = @columns

In dbml designer.cs file my stored procedure part of code:

[Function(Name="dbo.proc_StatsDay")]
public int proc_EasyDay([Parameter(DbType="NVarChar(64)")] string columns)
{
    IExecuteResult result = this.ExecuteMethodCall(this,((MethodInfo)MethodInfo.GetCurrentMethod())), columns);
    return ((int)(result.ReturnValue));
}

Answer 1

You'd run your select statement

SELECT ES.Location, DateAdd(dd, DateDiff(dd, 0, ES.OrderTime), 0),ES.Charge
FROM dbo.log_sales ES

and capture the result to a type like this

public class LogSale
{
  public string Location {get;set;}
  public DateTime OrderDate {get;set;}
  public decimal Charge {get;set;}
}

And then "pivot"/organize that in memory

List<LogSale> source = LoadData();
var pivot = source
  .GroupBy(ls => ls.OrderDate)
  .OrderBy(g => g.Key)
  .Select(g => new {
     Date = g.Key,
     Details = g
       .GroupBy(ls => ls.Location)
       .Select(loc => new {
          Location = loc.Key,
          Amount = loc.Sum(ls => ls.Charge)
       })
     });

Here's a second pivoty Linq which pushes the data into XML instead of anonymous types.

var pivot = source
  .GroupBy(ls => ls.OrderDate)
  .OrderBy(g => g.Key)
  .Select(g => new XElement("Date",
    new XAttribute("Value", g.key),
    g.GroupBy(ls => ls.Location)
     .Select(loc => new XElement("Detail",
       new XAttribute("Location", loc.Key),
       new XAttribute("Amount", loc.Sum(ls => ls.Charge))
     ))
  ));

Answer 2

Assuming truly dire dynamic need, you could use DataContext.ExecuteQuery

Just whip up a type that will cover the result space (the property names must match the column names in the query):

public class DynamicResult
{
  public DateTime OrderDate {get;set;}
  public decimal? Location1 {get;set;}
  public decimal? Location2 {get;set;}
//..
  public decimal? Location100 {get;set;}
}

Then call

IEnumerable<DynamicResult> result =
  myDataContext.ExecuteQuery<DynamicResult>(commandString, param1);

Answer 3

You can create your linq object for access after your returned dataset.

But would that really be of any use. Linq are usefull for typesafe calls and not dynamic results. You would not know what to look for compile time.