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();
}
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#