Get MaxRow Count in MYSQL / Get FOUND_ROWS and data table from Procedure with single call

As need to display Records in grid view in (ASP.NET) web site. and for the same i am using Procedure to return Data to .net Net Framework.

As my problem is that i need table set with return me the no of ROW's (Data) for the curr. Page. and i also need total no of row for paging (To display no of pages).

And for the开发者_如何学C same i have writen the following is the procedure which i have return

    CREATE PROCEDURE usp_SearchEmp(IN _RowIndex int, IN _MaxRows int , IN _SortByCol  varchar(40),IN _EmployeeID int)
 DECLARE _getLastSequenceNumberSQL VARCHAR(4000);
set _getLastSequenceNumberSQL ='
    SELECT SQL_CALC_FOUND_ROWS 
    @rownum:=@rownum+1 ROW,
        E.EmployeeID,
        E.EmployeeName  
    from
    (SELECT @rownum:=0) r, Employee E ;
        IF (_EmployeeID IS NOT NULL) THEN
              set _getLastSequenceNumberSQL = concat(_getLastSequenceNumberSQL,' WHERE (E.EmployeeID=',_EmployeeID,')');
        END IF;

        IF (_SortByCol IS NOT NULL) AND (_SortByCol != "") THEN
              set @SortCol = concat(' ORDER BY ', _SortByCol) ;
        ELSE
               set @SortCol = 'ORDER BY EmployeeName';
        END IF;

      set _getLastSequenceNumberSQL = concat(_getLastSequenceNumberSQL, @SortCol , ' LIMIT ',  _RowIndex, ' , ' , _MaxRows,' ');

 SET @getLastSequenceNumberSQL = _getLastSequenceNumberSQL;

  prepare lastRecordStmt from @getLastSequenceNumberSQL;
  execute lastRecordStmt;
  deallocate prepare lastRecordStmt;

    select FOUND_ROWS() as i;

---

But now the problem is that when i call this Procedure from .net it return me only the result of Found_Rows ..

 I need out put . some thing like the following 

like :-

SELECT 64 AS `ROWMAX`, E.EmployeeID, E.EmployeeName FROM Employee E order by EmployeeID LIMIT 0 , 4

|--------------------------------------------------------------|

| ROWMAX  | EmployeeID | EmployeeName                          |

|--------------------------------------------------------------|

|  64     |    1       | Emp One                               |

|  64     |    2       | Emp Two                               |

|  64     |    3       | Emp Three                             |

|  64     |    4       | Emp Four                              |

----------------------------------------------------------------

as i can user ROWMAX column to justified No. of Pages in grid view.

So please guide me out for the same. as i am new to MYSQL

---

As you can make dynamic Procedure . which will just count all the MaxNo of Row. and will return the same. and can call the same from different Procedure.'s

---

If you can use multi query you can have 2 result sets in the same procedure (i.e. add a SELECT FOUND_ROWS(); after your query and use LIMIT to display only one page results)

Here is a good post about implementing it in PHP: http://www.robert-gonzalez.com/2007/06/01/mysql-multiple-result-procs-in-php/

---

we can use other PROCEDURE Which will be call from MainProcedure(let say Main Procedure).

let says ProcedureCound it will contain Pr build sql query " SELECT COUNT(*) " and will accept two parameter (argument)

1) will be out parameter 2) will be From condition which is dynamic generated. in Main Procedure.

so now need to append second argument which contains from condition.which we got from Main Procedure.. and stored the result in to out parameter which we can access back into Main Procedure.