通用的SQL数据分页存储过程
CREATE PROCEDURE spGetPageOfRecords
@pageSize int = 20, -- 分页大小
@currentPage int , -- 第几页
@columns varchar(1000) = '*', -- 需要得到的字段
@tableName varchar(100), -- 需要查询的表
@condition varchar(1000) = '', -- 查询条件, 不用加where关键字
@ascColumn varchar(100) = '', -- 排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, -- 排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '' -- 主键名称
AS
BEGIN
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) -- 该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) -- 排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- 降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE -- 升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currentPage = 1 -- 第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM ' + @tableName + ' where ' + @condition+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END
@pageSize int = 20, -- 分页大小
@currentPage int , -- 第几页
@columns varchar(1000) = '*', -- 需要得到的字段
@tableName varchar(100), -- 需要查询的表
@condition varchar(1000) = '', -- 查询条件, 不用加where关键字
@ascColumn varchar(100) = '', -- 排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, -- 排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '' -- 主键名称
AS
BEGIN
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000) -- 该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) -- 排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- 降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE -- 升序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currentPage = 1 -- 第一页
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+
' '+@pkColumn+' FROM ' + @tableName + ' where ' + @condition+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END
执行代码如下:
-- Test Employees Table on Northwind database
EXEC spGetPageOfRecords 5, 2, 'EmployeeID, LastName, FirstName, Title, City, Region, Country', 'Employees', '', 'EmployeeID', 0, 'EmployeeID'
-- Test Customers Table on Northwind database
EXEC spGetPageOfRecords 10, 6, 'CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, Country', 'Customers', '', 'CustomerID', 0, 'CustomerID'
EXEC spGetPageOfRecords 5, 2, 'EmployeeID, LastName, FirstName, Title, City, Region, Country', 'Employees', '', 'EmployeeID', 0, 'EmployeeID'
-- Test Customers Table on Northwind database
EXEC spGetPageOfRecords 10, 6, 'CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, Country', 'Customers', '', 'CustomerID', 0, 'CustomerID'
技术改变世界