title: Converting simple T-SQL query to Paging enabled Query inside stored proc
body: <p>I am having following normal query inside my stored procedure</p>
  
  <pre><code>Select DISTINCT UI.UserId,UI.UserName, UI.FullName        
  From UserInfo as UI ,UserGroupRelation as UGR         
  Where UI.UserId = UGR.UserId AND UGR.GroupId = @pGroupId AND UI.Type = @pType    
  Order by UI.UserId ASC
  </code></pre>
  
  <p>and having these variables defined in stored proc</p>
  
  <pre><code>@pGroupId smallint, 
  @pType tinyint,
  @pStartIndex smallint,
  @pPageSize smallint
  </code></pre>
  
  <p>Now After I convert this query to paging enabled one , I wrote following query</p>
  
  <pre><code>SELECT UserTable.UserId,
            UserTable.UserName,
            UserTable.FullName
      From(
      Select ROW_NUMBER() OVER (
         ORDER BY UI.UserId,
                  UI.UserName,
                  UI.FullName ) as [Row_Number],
                  UI.UserId,
                  UI.UserName,
                  UI.FullName
         From UserInfo as UI,UserGroupRelation as UGR
         Where UI.UserId = UGR.UserId AND UGR.GroupId = @pGroupId AND UI.Type = @pType
         ORDER BY UI.UserId ASC ) as UserTable
         where UserTable.[Row_Number] BETWEEN @pStartIndex AND @pStartIndex + @pPageSize          
         ORDER BY UserTable.[Row_Number]
  </code></pre>
  
  <p>But SQL server is returnign error by saying:</p>
  
  <blockquote>
    <p>The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.</p>
  </blockquote>
  
  <p>Is there any other alternative or what is wrong with this query.</p>
  
  <p>It is running now when i added Top statement in sub Query like this</p>
  
  <pre><code>SELECT  UserTable.UserId,
            UserTable.UserName,
            UserTable.FullName
      From(
      Select Top(@pPageSize) ROW_NUMBER() OVER (
         ORDER BY UI.UserId,
                  UI.UserName,
                  UI.FullName ) as [Row_Number],
                  UI.UserId,
                  UI.UserName,
                  UI.FullName
         From UserInfo as UI,UserGroupRelation as UGR
         Where UI.UserId = UGR.UserId AND UGR.GroupId = @pGroupId AND UI.Type = @pType
         ORDER BY UI.UserId ASC ) as UserTable
         where UserTable.[Row_Number] BETWEEN @pStartIndex AND @pStartIndex + @pPageSize          
         ORDER BY UserTable.[Row_Number]
  </code></pre>
  
  <p>But i don't think it is the efficient one .is there some other efficient way.</p>