编写存储过程以获取 Jqgrid 分页数据的困难 [英] Difficulty in writing Stored Proc to fetch data for Jqgrid Pagination

问题描述

我正在使用以下 SQL 过程来获取数据以用于我的 Web 应用程序中 JqGrid 中的分页.

I am using the following SQL Procedure for getting data to use for pagination in JqGrid in my web Application.

ALTER PROCEDURE [dbo].[NewStoredProc] 
(
    @skip int,
    @pageSize int,
    @OrderBy Varchar(20),
    @OrderByDirection Varchar(10)
 )

AS
BEGIN
    DECLARE @records int;
    SET NOCOUNT ON;
    SET @records =(select count(*) from Data where Status='A');

    IF @skip <= 0
        SELECT TOP (@pageSize) * from Data where Status='A' 
        ORDER BY CASE WHEN @OrderBy='Column1' AND @OrderByDirection='D' THEN Column1 END DESC ,CASE WHEN @OrderBy='Column1' AND @OrderByDirection !='D'THEN Column1 END,
                 CASE WHEN @OrderBy='Column2' AND   @OrderByDirection='D' THEN Column2 END DESC ,CASE WHEN @OrderBy='Column2' AND @OrderByDirection !='D'THEN Column2 END,
                 CASE WHEN @OrderBy='Column3' AND   @OrderByDirection='D' THEN Column3 END DESC ,CASE WHEN @OrderBy='Column3' AND @OrderByDirection !='D'THEN Column3 END

    ELSE
        WITH GetAll AS (
           SELECT  * from Data where Status='A')
        ,GetFirst AS (
            SELECT TOP (@skip) *
            FROM GetAll
        ),GetNext AS (
            SELECT TOP (@pageSize) nt.*
            FROM GetAll AS nt
                LEFT OUTER JOIN GetFirst AS f ON f.Col1=nt.Col1
            WHERE f.Col1 IS  NULL)

        SELECT * FROM GetNext;
    RETURN @records;
END

这里，问题是我不能在 WITH 子句中使用 OrderBy.但是，我需要在排序后才能获取记录.有什么办法可以做到这一点..

Here, the Problem is i can not use OrderBy in WITH Clause. But, i need to get the records only after sorting.Is there any way we can do this..

简单说..如何实现order by for GetAll数据

Simply to say.. how to implement order by for GetAll data

推荐答案

在我看来，您可以通过使用 GetFirst 和 中的 OrderBy 来修复代码>GetNext 部分的 WITH 子句，但 不是 在 GetAll 部分.对应的代码大概如下

It seems to my that you can fix the code by usage of OrderBy in GetFirst and GetNext part of the WITH Clause, but not in the GetAll part. The corresponding code will be about the following

ALTER PROCEDURE [dbo].[NewStoredProc] 
(
    @skip int,
    @pageSize int,
    @OrderBy Varchar(20),
    @OrderByDirection Varchar(10)
 )

AS
BEGIN
    DECLARE @records int;
    SET NOCOUNT ON;
    SET @records =(select count(*) from Data where Status='A');

    IF @skip <= 0
        SELECT TOP (@pageSize) * from Data where Status='A' 
        ORDER BY CASE WHEN @OrderBy='Column1' AND @OrderByDirection='D'
                      THEN Column1
                 END DESC,
                 CASE WHEN @OrderBy='Column1' AND @OrderByDirection !='D'
                      THEN Column1
                 END,
                 CASE WHEN @OrderBy='Column2' AND @OrderByDirection='D'
                      THEN Column2
                 END DESC,
                 CASE WHEN @OrderBy='Column2' AND @OrderByDirection !='D'
                      THEN Column2
                 END,
                 CASE WHEN @OrderBy='Column3' AND @OrderByDirection='D'
                      THEN Column3
                 END DESC,
                 CASE WHEN @OrderBy='Column3' AND @OrderByDirection !='D'
                      THEN Column3
                 END
    ELSE
        WITH GetAll AS (
            SELECT * from Data where Status='A'
        ),GetFirst AS (
            SELECT TOP (@skip) *
            FROM GetAll
            ORDER BY CASE WHEN @OrderBy='Column1' AND @OrderByDirection='D'
                          THEN Column1
                     END DESC,
                     CASE WHEN @OrderBy='Column1' AND @OrderByDirection !='D'
                          THEN Column1
                     END,
                     CASE WHEN @OrderBy='Column2' AND @OrderByDirection='D'
                          THEN Column2
                     END DESC,
                     CASE WHEN @OrderBy='Column2' AND @OrderByDirection !='D'
                          THEN Column2
                     END,
                     CASE WHEN @OrderBy='Column3' AND @OrderByDirection='D'
                          THEN Column3
                     END DESC,
                     CASE WHEN @OrderBy='Column3' AND @OrderByDirection !='D'
                          THEN Column3
                     END
        ),GetNext AS (
            SELECT TOP (@pageSize) nt.*
            FROM GetAll AS nt
                LEFT OUTER JOIN GetFirst AS f ON f.Col1=nt.Col1
            WHERE f.Col1 IS  NULL
            ORDER BY CASE WHEN @OrderBy='Column1' AND @OrderByDirection='D'
                          THEN nt.Column1
                     END DESC,
                     CASE WHEN @OrderBy='Column1' AND @OrderByDirection !='D'
                          THEN nt.Column1
                     END,
                     CASE WHEN @OrderBy='Column2' AND @OrderByDirection='D'
                          THEN nt.Column2
                     END DESC,
                     CASE WHEN @OrderBy='Column2' AND @OrderByDirection !='D'
                          THEN nt.Column2
                     END,
                     CASE WHEN @OrderBy='Column3' AND @OrderByDirection='D'
                          THEN nt.Column3
                     END DESC,
                     CASE WHEN @OrderBy='Column3' AND @OrderByDirection !='D'
                          THEN nt.Column3
                     END
        )  
        SELECT * FROM GetNext

    RETURN @records;
END

这篇关于编写存储过程以获取 Jqgrid 分页数据的困难的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！