EntityFramework Database.ExecuteSqlCommandAsync ReturnValue Not working

c# entity-framework-6 stored-procedures

Question

using MVC 5, EF6 and c#
I have a stored procedure that accepts an input and returns a Value of 1 .(Simplified for Example) Strange part is that when I try to declare the Return value as ParameterDirection.Return Value I get an Error of

Must Declare the Scalar variable @ReturnVal

If I switch the SQLParameterDirection to OutPut it works fine. I'm just trying to understand why I can't use the ParameterDirection.ReturnValue?

Heres the code.

SP:

[dbo].TestProc ( @UserId int)
AS
BEGIN

return 1
end

And the c# Calling Code.

 public async Task<ActionResult> DoStuff()
    {

        using (var _context = new Model1())
        {
                SqlParameter sqlUserId = new SqlParameter("@UserId", 1) ;
                SqlParameter returnVal = new SqlParameter("@ReturnVal", SqlDbType.Int)
                {
                    Direction = ParameterDirection.ReturnValue
                };

           await  _context.Database.ExecuteSqlCommandAsync("exec @ReturnVal= [TestProc ] @UserId", returnVal, sqlUserId);
            var result  = (int)returnVal.Value;   // Return Correct Value. 
        }
        return View();
    }
1
0
5/8/2019 1:35:29 PM

Popular Answer

i think that you need to declare @ReturnValue in your SP and return that. something like

 [dbo].TestProc ( @UserId int, @ReturnValue int output)
AS
BEGIN
select @ReturnValue = 1    
end

i think the issue is that ReturnValue doesn't exist in your SP so it can't be matched when you are creating the SqlParameter in your C#

0
5/8/2019 5:26:59 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow