使用存储过程进行分页 [英] Pagination with the stored procedure
本文介绍了使用存储过程进行分页的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试添加分页存储过程的排序功能.
I am trying to add sorting feature of my pagination stored procedure.
我该怎么做,到目前为止我创建了这个.它工作正常,但是当传递 @sort
参数时,它不起作用.
How can I do this, so far I created this one. It works fine but when pass the @sort
parameter, it didn't work.
ALTER PROCEDURE [dbo].[sp_Mk]
@page INT,
@size INT,
@sort nvarchar(50) ,
@totalrow INT OUTPUT
AS
BEGIN
DECLARE @offset INT
DECLARE @newsize INT
IF(@page=0)
begin
SET @offset = @page;
SET @newsize = @size
end
ELSE
begin
SET @offset = @page+1;
SET @newsize = @size-1
end
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
WITH OrderedSet AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY @sort DESC) AS 'Index'
FROM [dbo].[Mk]
)
SELECT *
FROM OrderedSet
WHERE [Index] BETWEEN @offset AND (@offset + @newsize)
SET @totalrow = (SELECT COUNT(*) FROM [dbo].[Mk])
END
推荐答案
一种方法(可能不是最好的)是使用动态 SQL
One way (possibly not the best) to do it is to use dynamic SQL
CREATE PROCEDURE [sp_Mk]
@page INT,
@size INT,
@sort nvarchar(50) ,
@totalrow INT OUTPUT
AS
BEGIN
DECLARE @offset INT
DECLARE @newsize INT
DECLARE @sql NVARCHAR(MAX)
IF(@page=0)
BEGIN
SET @offset = @page
SET @newsize = @size
END
ELSE
BEGIN
SET @offset = @page*@size
SET @newsize = @size-1
END
SET NOCOUNT ON
SET @sql = '
WITH OrderedSet AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sort + ') AS ''Index''
FROM [dbo].[Mk]
)
SELECT * FROM OrderedSet WHERE [Index] BETWEEN ' + CONVERT(NVARCHAR(12), @offset) + ' AND ' + CONVERT(NVARCHAR(12), (@offset + @newsize))
EXECUTE (@sql)
SET @totalrow = (SELECT COUNT(*) FROM [Mk])
END
这是SQLFiddle演示
Here is SQLFiddle demo
这篇关于使用存储过程进行分页的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文