title: Accessing dynamically created stored procedure from LINQ
body: <p>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):</p>
  
  <blockquote>
    <p>OrderTime | Location1 | Location2 |
    Location3</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>Columns I use from <code>log_sales</code> table are:</p>
  
  <ul>
  <li>Location (various location which I'm pivoting),</li>
  <li>Charge (amount of money)</li>
  <li>OrderTime</li>
  </ul>
  
  <p>Stored procedure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In dbml <code>designer.cs</code> file my stored procedure part of code:</p>
  
  <pre><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));
  }
  </code></pre>