Entity Framework: how do I run a stored procedure and return a value?

Question

Is it possible to execute a stored procedure using the Entity Framework, passing in variables and returning data along with a return value?

Please do not leave any code about function mappings with Entity Framework.

I have tried this and I keep getting the same damn error:

Procedure or function 'usp_BusinessUser_Search' expects parameter '@TotalRecords', which was not supplied.

Here's my code:

SqlParameter Business = new SqlParameter("Business", Search.Term);
SqlParameter Location = new SqlParameter("Location", Search.Location);
SqlParameter PageNumber = new SqlParameter("PageNumber", Search.CurrentPage);
SqlParameter RecordsPerPage = new SqlParameter("RecordsPerPage", Search.RecordsPerPage);
SqlParameter TotalRecords = new SqlParameter("TotalRecords", 0);

SqlParameter parm = new SqlParameter()
{
    ParameterName = "@TotalRecords",
    Value = 0,
    SqlDbType = SqlDbType.Int,
    Direction = System.Data.ParameterDirection.Output
};

var List = db.ExecuteStoreQuery<ENT_SearchBusinessResult>("exec usp_BusinessUser_Search",Business,Location,PageNumber,RecordsPerPage,parm); 

Does anyone have any idea what is causing this?

Thanks

EDIT:

Stored procedure code:

ALTER PROCEDURE [dbo].[usp_BusinessUser_Search] (   @Business nVarChar(255) = NULL
                                                    , @Location nVarChar(255) = NULL
                                                    , @PageNumber Int = 1
                                                    , @RecordsPerPage Int = 10
                                                    , @TotalRecords Int OUTPUT)
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)
            , @CacheTable SYSNAME
            , @TotalRows Int
            , @Category VarChar(255)
            , @BusinessCategory Int 
            , @TownCounty VarChar(50)
            , @PcodeTownCounty VarChar(50)

    INSERT  Voucher_SearchHistory (Keyword, Location)
    SELECT  NULLIF(@Business,''), NULLIF(@Location,'')

Solution 1

This might help:

http://blogs.msdn.com/b/diego/archive/2012/01/10/how-to-execute-stored-procedures-sqlquery-in-the-dbcontext-api.aspx

Solution 2

You are not passing TotalRecords Sql Parameter in Excecute

var List = db.ExecuteStoreQuery<ENT_SearchBusinessResult>(
"exec usp_BusinessUser_Search",Business,
Location,PageNumber,RecordsPerPage,parm);

Solution 3

You can now use the following extension method, ExecuteSqlCommandWithReturn, that takes care of all of the work for you!

https://gist.github.com/copernicus365/7037320

string sql = "EXEC sp_CoolProc @SomeParam, @AnotherParam";
int returnValue;
int val = db.ExecuteSqlCommandWithReturn(sql, out returnValue, someParam, anotherParam);

The key to the solution was by Jieyang Hu here, which is this (though note that all of the following is fully handled by the aforementioned extension method): Just as you can do in a SQL prompt, you just set the result of the executed procedure to a SQL variable (in this case which is sent in as a parameter), like this:

EXEC @ReturnVal = sp_MyCoolProc;

This code adds the fragment @ReturnVal = after the first EXEC (followed by whitespace) it finds, and adds to the SqlParameters (or creates SqlParamters if there were none) a ReturnVal parameter, though the caller will never see these.