在 ModelBrowser 中添加 FunctionImport 时无法获取列信息

发布于 2024-10-12 15:47:26 字数 2450 浏览 4 评论 0原文

尽管我可以在 SQL Server Management Studio 中看到结果，但我无法从模型浏览器中的函数导入中获取列信息。我将 StoredProcedure 添加到模型浏览器中的 StoredProcedures 部分，当我添加函数导入时，我点击 GetColumnInformation，我收到此消息“所选存储过程不返回任何列”。但我正在执行存储过程，我可以看到结果。我的项目是一个 ASP.NET MVC2 项目。我正在使用 ADO.NET 实体模型。

这是我创建的存储过程

USE [MyDatabase Name] 去
设置 ANSI_NULLS 为开去将 QUOTED_IDENTIFIER 设置为 ON 转到

更改过程 [dbo].[我的存储过程名称] @startDate日期时间， @endDate日期时间， @accountId int， @healthPlanId int
作为开始
设置不计数；将 @variableId 声明为 int；创建表#TempAuditEvent( [AuditEvent_Id] [int] NOT NULL, [TableId] [int] NOT NULL, [原始记录] [xml] NOT NULL, [当前记录] [xml] NOT NULL, [ActionId] [int] NOT NULL, [发生时间] [日期时间] NOT NULL, [触发者] varchar NOT NULL, [IsActive] [tinyint] NOT NULL, [DateTime_Added] [日期时间] NOT NULL, [Added_By] varchar NOT NULL, [DateTime_Updated] [日期时间] NULL， [更新者] varchar NULL，）；声明变量游标 CURSOR FOR
从 ProductInfo p 中选择不同的 bao.ba_Object_id 加入 baSelection bas on p.Product_Id= bas.ba_BLID 在 bas.ba_variable_Id=bao.ba_Object_id 上加入 baObject bao 其中（bao.ba_Object_Type_Id = 4 或 bao.ba_Object_Type_Id = 6 或 bao.ba_Object_Type_Id = 22）且 p.AccountId = @accountId 且 p.IsProduct = 1 和 bas.Health_Plan_ID = @healthPlanId; 打开变量_光标；

从变量游标中获取下一个进入@variableId；

当 @@FETCH_STATUS = 0

开始时插入#TempAuditEvent SELECT * FROM AuditEvent WHERE AuditEvent_Id in 中（（从 vwProductChangesReport ae 中选择 ae.AuditEvent_Id 其中 ae.DateTime_Added >=@startDate AND ae.DateTime_Updated < @结束日期
AND ((ae.TableId >=12 AND ae.TableId<=14) OR ae.TableId=9 OR ae.TableId=40 OR ae.TableId=50 OR ae.TableId=64 OR ae.TableId=65) AND (ae.strCurrent LIKE '%Variable_Id="'+ CAST(@variableId AS varchar(20)) + '"%' 或 ae.strCurrent LIKE '%VariableId="'+ CAST(@variableId AS varchar(20)) + '"%' 或 ae.strCurrent LIKE '%BaObjectId="'+ CAST(@variableId AS varchar(20)) + '"%' 或 ae.strCurrent LIKE '%ParentBaObjectId="'+ CAST(@variableId AS varchar(20)) + '"%' 或 ae.strCurrent LIKE '%ObnId="'+ CAST(@variableId AS varchar(20)) + '"%' 或 ae.strCurrent LIKE '%Benefit_Variable_Id="'+ CAST(@variableId AS varchar(20)) + '"%' ）））

）从变量游标中获取下一个进入@variableId；结尾关闭变量光标；解除分配variable_cursor；

从#TempAuditEvent中选择*；删除表#TempAuditEvent；

结尾

原文

I am unable to get the column information from the function import in Model Browser even though I could see the results in SQL Server Management Studio. I added StoredProcedure to the StoredProcedures section in Model Browser and when I am adding the function import, I am hitting the GetColumnInformation, I am getting this "The selected stored procedure does not return no columns". But I am executing the stored procedure I could see the results.
My project is a ASP.NET MVC2 one. I am using ADO.NET Entity Model.

This is the stored proc I created

USE [MyDatabase Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[My Stored Proc Name]
@startDate datetime,
@endDate datetime,
@accountId int,
@healthPlanId int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @variableId as int;
CREATE TABLE #TempAuditEvent(
[AuditEvent_Id] [int] NOT NULL,
[TableId] [int] NOT NULL,
[Original_record] [xml] NOT NULL,
[Current_record] [xml] NOT NULL,
[ActionId] [int] NOT NULL,
[OccuredOn] [datetime] NOT NULL,
[TriggeredBy] varchar NOT NULL,
[IsActive] [tinyint] NOT NULL,
[DateTime_Added] [datetime] NOT NULL,
[Added_By] varchar NOT NULL,
[DateTime_Updated] [datetime] NULL,
[Updated_By] varchar NULL,
);
DECLARE variable_cursor CURSOR FOR
select distinct bao.ba_Object_id from ProductInfo p
join baSelection bas on p.Product_Id= bas.ba_BLID
join baObject bao on bas.ba_variable_Id=bao.ba_Object_id
where (bao.ba_Object_Type_Id = 4 or bao.ba_Object_Type_Id = 6 or bao.ba_Object_Type_Id = 22)
and p.AccountId = @accountId and p.IsProduct = 1
and bas.Health_Plan_ID = @healthPlanId;
OPEN variable_cursor;

FETCH NEXT FROM variable_cursor
INTO @variableId;

WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO #TempAuditEvent
SELECT * FROM AuditEvent WHERE AuditEvent_Id in
(
(SELECT ae.AuditEvent_Id FROM vwProductChangesReport ae
WHERE ae.DateTime_Added >=@startDate AND ae.DateTime_Updated < @endDate
AND ((ae.TableId >=12 AND ae.TableId<=14) OR ae.TableId=9 OR ae.TableId=40 OR ae.TableId=50 OR ae.TableId=64 OR ae.TableId=65)
AND (ae.strCurrent LIKE '%Variable_Id="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%VariableId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%BaObjectId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%ParentBaObjectId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%ObnId="'+ CAST(@variableId AS varchar(20)) + '"%'
OR ae.strCurrent LIKE '%Benefit_Variable_Id="'+ CAST(@variableId AS varchar(20)) + '"%'
))

)
FETCH NEXT FROM variable_cursor
INTO @variableId;
END
CLOSE variable_cursor;
DEALLOCATE variable_cursor;

SELECT * FROM #TempAuditEvent;
DROP TABLE #TempAuditEvent;

END

分享到QQ

分享到微博