编写Stored Proc来获取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..
简单地说..如何通过GetAll
数据实现顺序
Simply to say.. how to implement order by for GetAll
data
推荐答案
在我看来,您可以通过在WITH子句的GetFirst
和GetNext
部分中使用OrderBy
来修复代码,但是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
这篇关于编写Stored Proc来获取Jqgrid分页数据的困难的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!