title: how to write "WITH CTE AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CASE" in mysql procedure
body: <p>I have a SQL Server procedure and I need it to be converted to work in mysql.</p>
  
  <p>Below is my Mysql converted procedure that I tried but its not complete...</p>
  
  <pre><code>DELIMITER $$
  
  DROP PROCEDURE IF EXISTS `SelectjqGridUsers` $$
  CREATE PROCEDURE `SelectjqGridUsers` (IN PageIndex INT,IN SortColumnName VARCHAR(50),IN SortOrderBy VARCHAR(4) ,IN NumberOfRows INT ,OUT TotalRecords INT)
  
  BEGIN
  
   DECLARE StartRow INT;
   DECLARE CTE VARCHAR(100);
  
   SELECT  TotalRecords = ( SELECT COUNT(1) FROM  Users );
  
   SET StartRow = (PageIndex * NumberOfRows ) + 1 ;
  
  
  END $$
  
  DELIMITER ;
  </code></pre>
  
  <p>Here is my original SQL Server Procedure:</p>
  
  <pre><code>CREATE PROC [SelectjqGridUsers]
  @PageIndex INT ,
  @SortColumnName VARCHAR(50) ,
  @SortOrderBy VARCHAR(4) ,
  @NumberOfRows INT ,
  @TotalRecords INT OUTPUT
  AS 
  BEGIN
  
      SET NOCOUNT ON 
  
      SELECT  @TotalRecords = ( SELECT    COUNT(1)
                                FROM      [Users]
                              )
  
      DECLARE @StartRow INT
      SET @StartRow = ( @PageIndex * @NumberOfRows ) + 1 ;
  
  
      WITH    CTE
                AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY CASE
                                                            WHEN @SortColumnName = 'UserID'
                                                            AND @SortOrderBy = 'asc'
                                                            THEN UserID
                                                           END ASC, CASE
                                                            WHEN @SortColumnName = 'UserID'
                                                            AND @SortOrderBy = 'desc'
                                                            THEN UserID
                                                            END DESC, CASE
                                                            WHEN @SortColumnName = 'UserName'
                                                            AND @SortOrderBy = 'asc'
                                                            THEN UserName
                                                            END ASC, CASE
                                                            WHEN @SortColumnName = 'UserName'
                                                            AND @SortOrderBy = 'desc'
                                                            THEN UserName
                                                            END DESC, CASE
                                                            WHEN @SortColumnName = 'FirstName'
                                                            AND @SortOrderBy = 'asc'
                                                            THEN FirstName
                                                            END ASC, CASE
                                                            WHEN @SortColumnName = 'FirstName'
                                                            AND @SortOrderBy = 'desc'
                                                            THEN FirstName
                                                            END DESC , CASE
                                                            WHEN @SortColumnName = 'MiddleName'
                                                            AND @SortOrderBy = 'asc'
                                                            THEN MiddleName
                                                            END ASC, CASE
                                                            WHEN @SortColumnName = 'MiddleName'
                                                            AND @SortOrderBy = 'desc'
                                                            THEN MiddleName
                                                            END DESC , CASE
                                                            WHEN @SortColumnName = 'LastName'
                                                            AND @SortOrderBy = 'asc'
                                                            THEN LastName
                                                            END ASC, CASE
                                                            WHEN @SortColumnName = 'LastName'
                                                            AND @SortOrderBy = 'desc'
                                                            THEN LastName
                                                            END DESC, CASE
                                                            WHEN @SortColumnName = 'EmailID'
                                                            AND @SortOrderBy = 'asc'
                                                            THEN EmailID
                                                            END ASC, CASE
                                                            WHEN @SortColumnName = 'EmailID'
                                                            AND @SortOrderBy = 'desc'
                                                            THEN EmailID
                                                            END DESC ) AS RN ,
                              UserID ,
                              UserName ,
                              FirstName ,
                              MiddleName ,
                              LastName ,
                              EmailID
                     FROM     [Users]
                   )
          SELECT  UserID ,
                  UserName ,
                  FirstName ,
                  LastName ,
                  MiddleName ,
                  EmailID
          FROM    CTE
          WHERE   RN BETWEEN @StartRow - @NumberOfRows
                     AND     @StartRow - 1
  
      SET NOCOUNT OFF
  
  
  END
  </code></pre>
  
  <p>Can anyone please help me finish this?</p>