How i can get select rows and rows count

Question

Hi everyone i am try creat pageing for some entity i have function whith signature like this public static List<someClass> somePageingFunc(int curentPage,int pageSize,int out maxCount) i can't use stored procedur i am new in sql please tell me what I'm doing wrong

string comandStr = "DECLARE @MaxCount int SELECT @MaxCount= Count (*) FROM tbMediaItems"+
                                    " SELECT top(@NumOfRows) * FROM ( SELECT RowNum = ROW_NUMBER() OVER ("+
                                    "ORDER BY Artist), * FROM tbMediaItems) AS a WHERE RowNum > (@NumOfRows * (@CurrentPage - 1)) RETURN @MaxCount";
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = comandStr;
                cmd.CommandType = CommandType.Text;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@NumOfRows", SqlDbType.Int).Value = pageSize;
                cmd.Parameters.Add("@CurrentPage", SqlDbType.Int).Value = curentPage;
                cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = 

ParameterDirection.ReturnValue;
try
                {
                    //SqlDataReader reder = cmd.ExecuteReader();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        var ss = reader[0];
                        var bb = reader[1];
                    }
                    maxCount = Convert.ToInt32(cmd.Parameters["@Result"].Value);
                }
                catch (Exception ex)
                {

                }

I get Exeption Message = A RETURN statement with a return value cannot be used in this context. When i remove RETURN @MaxCount all work ok but i am not get MaxCount

Mohammad Arshad Alam · Answer 1 · 2013-04-11T08:31:56.237

2

Update your code and use ParameterDirection.Output to get return value :

cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = 
ParameterDirection.ReturnValue;

to

 cmd.Parameters.Add("@Result", SqlDbType.Int).Direction  = 
 ParameterDirection.Output;

Edit 1:

you can create store-procedure as follows:

CREATE PROC [dbo].[procGetMaxCount]
@NumOfRows int,
@CurrentPage int,
@Result int OUTPUT  
AS    
BEGIN   
    SELECT @Result= Count (*) FROM tbMediaItems
                      SELECT top(@NumOfRows) * FROM ( SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Artist), * FROM tbMediaItems) AS a WHERE RowNum > (@NumOfRows * (@CurrentPage - 1))                                       
END

and update your .cs file as :

 string comandStr="procGetMaxCount";

and cmd.CommandType = CommandType.StoredProcedure;

edited Apr 11 '13 at 08:31

answered Apr 11 '13 at 08:11

Mohammad Arshad Alam

9,694
6
38
61

Thanks but unfortunately i get same Exception – HaikMnatsakanyan Apr 11 '13 at 08:15
cant you convert your `select` statement as a store procedure – Mohammad Arshad Alam Apr 11 '13 at 08:19
exactly,but when i remove Return MaxCount from query string – HaikMnatsakanyan Apr 11 '13 at 08:34
Thanks but i cant use Stored procedur – HaikMnatsakanyan Apr 11 '13 at 09:14
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/28013/discussion-between-arshad-and-haikmnatsakanyan) – Mohammad Arshad Alam Apr 11 '13 at 09:22

score 1 · Accepted Answer · edited May 23 '17 at 11:56

RETURN is used within stored procedures to exit and indicate a status at the same time. You CANNOT use it simply within a query batch.

For your use case, I would probably do one of two things:

Use two queries in a batch. You can use reader.NextResult to move between multiple result sets in a batch.
Use two independent SqlCommands

A little more complicated, but you can also add an additional column to the result set, e.g.

SELECT top(@NumOfRows) B.*, A.TotalRows
FROM (SELECT TotalRows = COUNT(*) FROM tbMediaItems) A
CROSS JOIN ( SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Artist) ...

Although the COUNT(*) in here or in your original could be replaced with this type of query on the sys.partitions table if your table is very large. It is mostly accurate except for very highly transactional tables.

Thanks for your answer I used first case with reader.NextResult just it simple — HaikMnatsakanyan, Apr 11 '13 at 09:12

How i can get select rows and rows count

2 Answers2

Edit 1: