当前位置: 动力学知识库 > 问答 > 编程问答 >

asp.net - 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.

网友答案:

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

网友答案:

Move the orderby to outside the WITH block.

网友答案:

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.
网友答案:

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


分享给朋友:
您可能感兴趣的文章:
随机阅读: