使用实体框架，是否应在存储过程中使用RETURN或SELECT？ [英] Using Entity Framework, should I use RETURN or SELECT in my stored procedures?
问题描述
我当前正在尝试将存储过程与Entity Framework一起使用，并且遇到返回类型问题。
I am currently trying to use stored procedures with Entity Framework and I am encountering a problem with return type.
我的存储过程的返回类型为BIT。
The return type of my stored procedure is BIT.
这是我的存储过程的代码（简化）：
Here is the code (simplified) of my stored procedure :
CREATE PROCEDURE [dbo].[sp_makeStuff]
@id int
AS
BEGIN
DECLARE @status BIT
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM MyTable WHERE Id = @id
SET @status = 1
COMMIT
END TRY
BEGIN CATCH
SET @status = 0
ROLLBACK
END CATCH
RETURN @status
END
END
我在.edmx文件中添加了sp_makeStuff，返回类型为bool？。
I added sp_makeStuff in my .edmx file with return type bool?.
然后，我尝试使用下面的代码执行它
Then, I try to execute it with the code below
using (var ctx = new MyEntities())
{
ObjectResult<bool?> result = ctx.sp_makeStuff(id);
if (result.FirstOrDefault().Value)
{
// Stuff
}
}
但是，它引发类型为System.Data.EntityCommandExecutionException的异常，其消息是商店数据提供者返回的数据读取器没有足够的列用于查询请求。
However, it raises an exception of type System.Data.EntityCommandExecutionException, whose message is "The data reader returned by the store data provider does not have enough columns for the query requested."
经过一番调查，我发现如果替换
After some investigation, I found that it works if I replace
RETURN @status
by
SELECT @status
过去，我一直使用T-SQL的 RETURN关键字返回这样的结果。
In the past, I always used the T-SQL "RETURN" keyword to return such a result.
我想知道我的新实现是否正确。
因为我想在项目中使用EF，是否有使其与 RESULT一起使用的方法，还是我必须明确地使用 SELECT？
I am wondering if my new implementation is correct. As I want to use EF in my projects, is there a mean to make it work with "RESULT" or do I have to use "SELECT" definitively ?
谢谢。
编辑2014年10月24日
根据回复在下面，我重新编写了存储过程。如果出现问题，它不会返回任何内容，而是会引发错误。
Based on responses below, I re-wrote my stored procedure. Instead of returning something, it raises an error if something goes wrong.
CREATE PROCEDURE [dbo].[sp_makeStuff]
@id int
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM MyTable WHERE Id = @id
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
RAISERROR('Delete failed', 16, -1)
END CATCH
END
END
然后，我告诉EF我的存储过程未返回任何内容，
Then, I told to EF that my stored procedure returns nothing, and I deal with the exception if RAISERROR is called.
try
{
ctx.sp_web_DeleteElement(elementId);
// ...
}
catch (Exception e) // Message in e.InnerException.Message
{
// ...
}
推荐答案
，我总是使用T-SQL RETURN关键字来返回这样的
结果。
In the past, I always used the T-SQL "RETURN" keyword to return such a result.
存储过程返回代码只能是整数。其目的是向调用应用程序指示成功或失败，而不是返回数据。使用OUTPUT参数或结果集返回数据。
A stored procedure return code can only be an integer. Its purpose is to indicate success or failure to the calling application, not to return data. Return data using an OUTPUT parameter or result set.
返回值约定是零表示成功，对于错误/警告，非零。非零值也可以用来指示特定的特定原因（例如，在尝试更新时未找到99的客户），以代替引发错误。默认情况下，如果引发严重性为10或更高的任何错误，SQL Server将返回负值。
The return value convention is that a zero indicates success and non-zero for error/warning. The non-zero value may also be used to indicate specific the specific cause (e.g. 99 for customer not found during an update attempt) as an alternative to raising an error. By default, SQL Server will return a negative value if any errors with severity of 10 or higher were raised.
这篇关于使用实体框架，是否应在存储过程中使用RETURN或SELECT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
