How to map stored procedures having dynamically pivoted fields
I have a stored procedure which gets pivot
fields as a parameter (say @P
) which splits by ,
and am pivoting for a table based on fields which the the @P
includes them , i wanna map the stored procedure in EF
. But don't know how to create a complex type with dynamic number of fields based on result of stored procedure.
I have something like the fallowing:
CREATE PROC pr
@p AS NVARCHAR(500)='[250], [251], [256], [257], [260]'
AS
DECLARE @qy NVARCHAR(500)
SET @qy='SELECT VendorID, '+dbo.SetAliasForPivotingCols(@p)+'
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( '+@p+' )
) AS pvt
ORDER BY pvt.VendorID;
EXEC sp_executesql @qy
To attach alias after pivoted fields (imagine im not doing this if its complex) :
CREATE FUNCTION [dbo].[SetAliasForPivotingCols](@RequestedRscsId NVARCHAR(500))RETURNS `NVARCHAR(2000)`
BEGIN
SET @RequestedRscsId=','+@RequestedRscsId
DECLARE @ReturnValue NVARCHAR(2000)=''
DECLARE @CloseBraceIndex INT
DECLARE @Alias NVARCHAR(10)
WHILE LEN(@RequestedRscsId)>1
BEGIN
SET @CloseBraceIndex=PATINDEX('%]%',@RequestedRscsId)
SET @Alias=SUBSTRING(@RequestedRscsId,2,@CloseBraceIndex-1)
SET @RequestedRscsId =SUBST开发者_如何学运维RING(@RequestedRscsId,@CloseBraceIndex+1,LEN(@RequestedRscsId)-@CloseBraceIndex+1)
SET @ReturnValue+=',SUM('+@Alias+')'+' AS '+SUBSTRING(@Alias,1,LEN(@Alias))
END
RETURN SUBSTRING(@ReturnValue,2,LEN(@ReturnValue))
END
any suggestion?
thank you.
It is not possible. You must consume such stored procedure with classic ADO.NET.
精彩评论