CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-procedures
Funky
Funky

Reputation: 13608

ExecuteStoreQuery Output parameter not being returned

I have some entity framework code to run a stored procedure which returns a parameter. Every time I run the code the parameter comes back as null. Does anyone have any ideas what could be causing this?

Thanks

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);                  


                var TotalRecords = new SqlParameter
                {
                    ParameterName = "TotalRecords",
                    Value = 0,
                    Direction = ParameterDirection.Output                        
                };



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

I used Sql profiler and found it was doing the following:

declare @p7 int
set @p7=53
exec sp_executesql N'exec usp_BusinessUser_Search    @Business,
@Location,@PageNumber,@RecordsPerPage,
@TotalRecords out',
N'@Business  nvarchar(14),@Location nvarchar(14),
@PageNumber int,
@RecordsPerPage int,@TotalRecords int output',
@Business=N'Food 
and Drink',@Location=N'United   Kingdom',@PageNumber=1,@RecordsPerPage=10,
@TotalRecords=@p7 output
select @p7

this is fine, it shows the return value if you run this query but is not returning back to my code :(

Upvotes: 3

Views: 3268

Answers (2)

user2623955
user2623955

Reputation: 175

That is not necessarily true. Here is my working example with output params:

                            private IEnumerable<AppealsGridViewModel> GetDataNew(string sortString, int pageNumber, int pageSize)
                    {
                        var totalRowCount = new SqlParameter
                        {
                            ParameterName = "@TotalRowCount",
                            SqlDbType = SqlDbType.Int,
                            Direction = ParameterDirection.Output
                        };

                        var totalPropertyCount = new SqlParameter
                        {
                            ParameterName = "@TotalPropertyCount",
                            SqlDbType = SqlDbType.Int,
                            Direction = ParameterDirection.Output
                        };

                        var paramList = new object[] {
                            new SqlParameter {ParameterName = "@CompanyId", SqlDbType = SqlDbType.Int, Value = 5}
                            , new SqlParameter {ParameterName = "@AccountId", SqlDbType = SqlDbType.Int, Value = 0}
                            , new SqlParameter {ParameterName = "@IsServiceCenterUser", SqlDbType = SqlDbType.Bit, Value = 0}
                            , new SqlParameter {ParameterName = "@PageNumber", SqlDbType = SqlDbType.Int, Value = (pageNumber + 1)}
                            , new SqlParameter {ParameterName = "@PageSize", SqlDbType = SqlDbType.Int, Value = pageSize}
                            , new SqlParameter {ParameterName = "@SortOrder", SqlDbType = SqlDbType.VarChar, Value = sortString}
                            , totalRowCount
                            , totalPropertyCount
                        };

                        var s = _dataContext.ExecuteStoreQuery<AppealsGridViewModel>("exec atAppealsSearch @CompanyId, @AccountId, @IsServiceCenterUser, @PageNumber, @PageSize, @SortOrder, @TotalRowCount out, @TotalPropertyCount out", paramList).ToList();

                        var appealCount = totalRowCount.Value;
                        var propertyCount = totalPropertyCount.Value;

                        return s;
                    }

            ALTER PROCEDURE [dbo].[atAppealsSearch]
                @CompanyId INT
                , @AccountId INT = 0
                , @IsServiceCenterUser BIT = 0
                , @PageNumber INT = 1
                , @PageSize INT = 25
                , @SortOrder VARCHAR(MAX)
                , @TotalRowCount int OUT
                , @TotalPropertyCount int OUT
                AS


            SELECT @TotalRowCount = 33124
            SELECT @TotalPropertyCount = 555

            EXEC SP_EXECUTESQL @Query

Upvotes: 0

jlew
jlew

Reputation: 10601

This article says that you need to read all the results before trying to access the value of an out parameter. Your code isn't complete enough to tell whether you are doing this (try ToList() if you are not.)

Upvotes: 3

Related Questions