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
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.
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