实体框架 4:所选存储过程不返回任何列
在我的 SP 中,我混合了静态 SQL 和动态 SQL:
declare @result table
(
RowNum bigint,
Id_and_Title varchar(max),
DaysLeft int,
cat_id int
);
然后,在动态 SQL 中,我将结果插入到该表中:(
DECLARE @TSQL NVARCHAR(max);
SET @TSQL = ......
我使用 print @TSQL
所以我确信查询没问题)
insert into @result
EXECUTE sp_executesql @TSQL
select * from @result
但是,当我尝试在 VS 2010 Ultimate 中导入 taht SP 时,我看到了标题中提到的消息。是什么原因造成的呢?我已经多次出现该错误,但我仍然不知道是什么原因导致的
In my SP, I mix static SQL and dynamic SQL:
declare @result table
(
RowNum bigint,
Id_and_Title varchar(max),
DaysLeft int,
cat_id int
);
then, in the dynamic SQL I insert the result to that table:
DECLARE @TSQL NVARCHAR(max);
SET @TSQL = ......
(I use print @TSQL
so I'm sure that the query is OK)
insert into @result
EXECUTE sp_executesql @TSQL
select * from @result
but, when I try to import taht SP in VS 2010 Ultimate I see the message as I mentioned in the title. What causes that ? For many times I've occured that error but I still don't know what causes that
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
尝试将此行添加到存储过程的开头:
设置 FMTONLY 关闭
完成导入后,您可以将其删除。
这篇文章中提到了它(有点长),尽管它对我有用当我严重依赖存储过程时,我最终回到了 ADO.NET。请记住,如果您的列根据动态 SQL 进行更改,您的实体模型将会崩溃。
Try adding this line to the beginning of your stored procedure:
SET FMTONLY OFF
You can remove this after you have finished importing.
It's mentioned in this article (kind of a long way down) and it worked for me, although I have ended up going back to ADO.NET for the times when I am relying heavily on Stored Procedures. Bear in mind that if your columns change depending on the dynamic SQL your entity model will break.
我不知道您的返回类型到底是什么,但如果您的返回类型数量有限(且相对较少),您可以创建一个用户定义的表类型并返回它。
然后在过程中:
现在 EF 应该能够识别返回的列类型。
I don't know exactly what your return type is, but if you have a finite (and relatively small) number of return types, you can create a User-Defined Table Type and return that.
Then in the procedure:
Now EF should be able to recognize the returned columns type.