CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedurespagination
Abhi
Abhi

Reputation: 5557

Converting simple T-SQL query to Paging enabled Query inside stored proc

I am having following normal query inside my stored procedure

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

and having these variables defined in stored proc

@pGroupId smallint, 
@pType tinyint,
@pStartIndex smallint,
@pPageSize smallint

Now After I convert this query to paging enabled one , I wrote following query

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]

But SQL server is returnign error by saying:

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.

Is there any other alternative or what is wrong with this query.

It is running now when i added Top statement in sub Query like this

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]

But i don't think it is the efficient one .is there some other efficient way.

Upvotes: 0

Views: 470

Answers (1)

Abhi
Abhi

Reputation: 5557

Finally I found following Query as final and efficient one

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 ) as UserTable
       where UserTable.[Row_Number] BETWEEN @pStartIndex AND @pStartIndex + @pPageSize -1           
       ORDER BY UserTable.[Row_Number]

Upvotes: 2

Related Questions