Databases
StackOverflow2013
Postsdbo
POAccessing dynamically created stored procedure from LINQ
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccessing dynamically created stored procedure from LINQ
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload