CODEWITHSUNDEEP
asp.netsql-server
IrfanRaza
IrfanRaza

Reputation: 3058

Error in Stored Procedure

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.

Upvotes: 1

Views: 96

Answers (4)

Chuckie
Chuckie

Reputation: 135

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

Upvotes: 0

Steve Homer
Steve Homer

Reputation: 3922

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.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166326

Move the orderby to outside the WITH block.

Upvotes: 1

David Hedlund
David Hedlund

Reputation: 129782

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

Upvotes: 6

Related Questions