从EF 4.0中的存储过程返回输出和返回值 [英] Returning output and return value from stored procedure in EF 4.0

问题描述

你好



这是一个关于EF和SQL存储过程的快速问题，虽然它不会让我感到烦恼但我想知道答案。



是否可以使用存储过程在EF中获得返回值和输出值。

Hello

This is one quick question about EF and SQL stored procedures, although it doesn''t bother me much I would like to know the answer.

Is it possible to get return value and output value in EF using stored procedure.

CREATE PROCEDURE [dbo].[CheckUser] 
	@UserName varchar(8),
	@Password varchar(12),
        @User_ID int OUTPUT
AS
BEGIN
	SELECT @User_ID = User_ID FROM [User] WHERE UserName = @UserName AND Password = @Password
	SELECT @User_ID
	
	IF @User_ID IS NOT NULL
		RETURN 0
	
	DECLARE @TempUser_ID_1 int
	DECLARE @TempUser_ID_2 int
	
	SELECT @TempUser_ID_1 = User_ID FROM [User] WHERE UserName = @UserName
	SELECT @TempUser_ID_2 = User_ID FROM [User] WHERE Password = @Password
	
	IF @TempUser_ID_1 IS NULL AND @TempUser_ID_2 IS NULL
		RETURN -1
			 
	IF @TempUser_ID_1 IS NULL
		RETURN -2
		
	IF @TempUser_ID_2 IS NULL
		RETURN -3					 
END

当我在MSSMS中运行此程序时运行正常并给我我想要的结果，但是当我尝试通过EF运行时我得到了这个错误：

When I run this procedure in MSSMS it runs fine and give me the results I want, but when I try to run it through EF I got this error:

The data reader returned by the store data provider does not have enough columns for the query requested.

我将导入函数的返回值设置为< b> int32 并且我理解'问题所在的位置。

当我将返回值设置为 none 时，我获得了良好的输出值但是return总是-1，h功能没有返回任何东西。



我确实找到了一个解决方案，现在我没有使用输出参数，但我还在徘徊是否有可能得到两者，因为我相信它不是，但不确定，我确实需要你的意见来消除我的怀疑。



谢谢，Mirza

I set the return value of imported function to int32 and as I understand that''s where the problem lays.
When I set the return value to none I get good output value but the return is always -1, hence the function doesn''t return anything.

I did found a solution and now I don''t use an output parameter, but I''m still wandering is it possible to get both since I believe it''s not, but not sure, I do need Your opinions to clear my doubts.

Thanks , Mirza

推荐答案

将返回值设置为none时有一个解决方案。您可以使用选择。



试试这个

There is a solution when setting return value to none. You can use select.

Try this

CREATE PROCEDURE [dbo].[CheckUser] 
	@UserName varchar(8),
	@Password varchar(12),
        @User_ID int OUTPUT
AS
BEGIN
        DECLARE @ret int
	SELECT @User_ID = User_ID FROM [User] WHERE UserName = @UserName AND Password = @Password
	SELECT @User_ID
	
	IF @User_ID IS NOT NULL
		select @ret=0
	
	DECLARE @TempUser_ID_1 int
	DECLARE @TempUser_ID_2 int
	
	SELECT @TempUser_ID_1 = User_ID FROM [User] WHERE UserName = @UserName
	SELECT @TempUser_ID_2 = User_ID FROM [User] WHERE Password = @Password
	
	IF @TempUser_ID_1 IS NULL AND @TempUser_ID_2 IS NULL
		select @ret=-1
			 
	IF @TempUser_ID_1 IS NULL
		select @ret=-2
		
	IF @TempUser_ID_2 IS NULL
		select @ret=-3		
select @ret		 
END

这篇关于从EF 4.0中的存储过程返回输出和返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！