Error in Stored Procedure

Question

Hello friends,

I am trying to create an SP for presenting paged data on aspx page. I have written following code -

Create PROCEDURE [dbo].[sp_GetAllAssignmentData_Paged] 

    @currentPage INT=1, 
    @pageSize INT=20

AS

BEGIN

    SET NOCOUNT ON;
    with AssignmentData As(
     select ROW_NUMBER() over (order by a.StockNo desc) AS [Row], 
      a.StockNo,c.ClaimNo,v.[Year],v.Make,v.Model,
      c.DOAssign,c.InsuranceComp,c.Location,c.Status
     from 
      dbo.Assignments a,
      dbo.Assignment_ClaimInfo c,
      dbo.Assignment_VehicleInfo v
     where
      (a.AssignmentID=c.AssignmentID) and
      (v.AssignmentID=c.AssignmentID)
       order by a.StockNo desc
    )
    SELECT StockNo, ClaimNo, [Year], Make, Model, DOAssign, InsuranceComp, Location, [Status]
    FROM AssignmentData
    WHERE Row between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize) END

---

When I try to create this SP following error message is generated - 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.

Could someone correct my mistake?

Thanks for sharing your valuable time.

Answer 1

The ordering of your inner set, AssignmentData, is pointless, since it is the selection from that set that will determine the ordering. Therefore, it is not allowed. Move the lline

order by a.StockNo desc

to your final select

Answer 2

Move the orderby to outside the WITH block.

Answer 3

Your CTE has an order by at the end that's invalid - take this out and all should be well.

    select ROW_NUMBER() over (order by a.StockNo desc) AS [Row],  
            a.StockNo,c.ClaimNo,v.[Year],v.Make,v.Model, 
            c.DOAssign,c.InsuranceComp,c.Location,c.Status 
    from  
            dbo.Assignments a, 
            dbo.Assignment_ClaimInfo c, 
            dbo.Assignment_VehicleInfo v 
    where 
            (a.AssignmentID=c.AssignmentID) and 
            (v.AssignmentID=c.AssignmentID) 
             order by a.StockNo desc -- This is the problem.

Answer 4

Create PROCEDURE [dbo].[sp_GetAllAssignmentData_Paged] @currentPage INT=1, @pageSize INT=20ASBEGIN SET NOCOUNT ON; with AssignmentData As( select ROW_NUMBER() over (order by a.StockNo desc) AS [Row], a.StockNo,c.ClaimNo,v.[Year],v.Make,v.Model, c.DOAssign,c.InsuranceComp,c.Location,c.Status from dbo.Assignments a, dbo.Assignment_ClaimInfo c, dbo.Assignment_VehicleInfo v where (a.AssignmentID=c.AssignmentID) and (v.AssignmentID=c.AssignmentID) ) SELECT StockNo, ClaimNo, [Year], Make, Model, DOAssign, InsuranceComp, Location, [Status] FROM AssignmentData WHERE Row between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize) order by a.StockNo desc
END

---