下面的是SqlServer 通用分页查询的存储过程，不过此方法只适合单表查询。
CREATE PROCEDURE CommonPageIndexSearch@fileds NVARCHAR(200)='*',--需要查询的字段@orderField NVARCHAR(10)='Id',--排序字段，一般分页查询都用主键作为排序字段@tableName NVARCHAR(20),--需要查询的表名@condition NVARCHAR(400),--查询条件，如：AND 1=1@begin NVARCHAR(10),--分页查询开始行@end NVARCHAR(10)----分页查询结束行ASBEGIN DECLARE @SQL NVARCHAR(4000); SET @SQL='SELECT * FROM (SELECT ROW_NUMBER(ORDER BY '+@orderField+') AS Line' +@fileds+' FROM '+@tableName +' WHERE 1=1 '+@condition+')A WHERE A.Line>'+@begin+' AND A.Line<='+@end; EXECUTE(@SQL);END
有个更复杂的通用分页查询存储过程,结合了正则表达式，支持多表联接查询
GO/****** Object: StoredProcedure [dbo].[CustomPaged] Script Date: 2013/11/4 0:42:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--DROP PROC[dbo].[CustomPaged]goCREATE PROC [dbo].[CustomPaged] @tablename NVARCHAR(100) = NULL ,--所要查询的表或视图名 不可以设置表别名 表名在关系表中设置 tablename a @nexusStr NVARCHAR(1000) = '' ,--关系表 left join tablename b on a.columnname=b.columnname @columnsName NVARCHAR(1000) = '*' ,--查询所需的列名 @rankOrder NVARCHAR(2000) = '' ,--流水号排序列以及方式 @currentIndex INT = 1 ,--页码 按正常页面写参数就可以了 @pagesize INT = 10 ,--每页的行数量 @where NVARCHAR(2000) = '' ,--查询条件 @order NVARCHAR(2000) = '' ,--排序列 @AllCount INT OUTPUT--jieguohangAS BEGIN IF ( ISNULL(@tablename, '') != '' ) BEGIN SET nocount ON; DECLARE @tempTableName VARCHAR(1000); SELECT @tempTableName = '[##' + RTRIM(LTRIM(REPLACE(hostname, '-', '_'))) + '_' + RTRIM(LTRIM(net_address)) + '_' + dbo.regexReplace(@tablename, '[^\w]', '_', 0, 0) + '_' + CAST(@@SPID AS VARCHAR(10)) + ']' FROM master..sysprocesses WHERE spid = @@SPID IF ( ISNULL(@columnsName, '') = '' ) SET @columnsName = '*'; IF ( ISNULL(@rankorder, '') = '' ) BEGIN --判断该表是否存在主键 IF EXISTS ( SELECT keyno FROM sysindexkeys WHERE id = OBJECT_ID(@tablename) ) BEGIN SELECT @rankorder = name FROM SysColumns WHERE id = OBJECT_ID(@tablename) AND colid = ( SELECT TOP 1 keyno FROM sysindexkeys WHERE id = OBJECT_ID(@tablename) ) END ELSE BEGIN SELECT TOP 1 @rankorder = name FROM SysColumns WHERE id = OBJECT_ID(@tablename) END END DECLARE @sql NVARCHAR(2000) SET @sql = 'select row_number() over(order by ' + @rankOrder + ') as rankid,' + @columnsName + ' into ' + @tempTableName + ' from ' + @tablename; IF ( ISNULL(@nexusStr, '') != '' ) SET @sql = @sql + ' ' + @nexusStr; --添加查询条件 IF ( LEN(@where) > 0 ) BEGIN SET @sql = @sql + ' where ' + @where; END --添加排序列 IF ( LEN(@order) > 0 ) BEGIN SET @sql = @sql + ' order by ' + @order END --动态执行sql语句 EXEC(@sql); DECLARE @totality INT; SET @sql = 'select @total=COUNT(*) from ' + @tempTableName; EXEC sp_executesql @sql, N'@total int output', @totality OUTPUT; --验证当查询的前页是否超出查询的页，超过则返回查询也的第一页 DECLARE @IsHavePage BIT; IF ( @totality % @pagesize > 0 ) SET @IsHavePage = 1; ELSE SET @IsHavePage = 0; IF @totality / @pagesize + CAST(@IsHavePage AS INT) < @currentIndex--当前总页数/每页显示数 SET @currentIndex = 1; ELSE --在排序页中的页码索引应该从0开始为第一页 SET @currentIndex = @currentIndex - 1; SET @sql = 'select * from ' + @tempTableName + ' where rankid>' + CONVERT(VARCHAR(10), @currentIndex * @pagesize) + ' and rankid<=' + CONVERT(VARCHAR(10), @currentIndex * @pagesize + @pagesize); SET @sql = @sql + ';drop table ' + @tempTableName; EXEC(@sql); SET nocount OFF; SET @AllCount = ISNULL(@totality, 0); END ENDGO
在调用上面的存储过程之前，必须先在数据库中执行以下方法
--正则替换 --开启xp_cmdshell --不开启会提示：SQL Server blocked access to procedure 'xp_cmdshell' go sp_configure 'show advanced options', 1 go reconfigure go sp_configure 'xp_cmdshell', 1 go reconfigure --开启sp_OACreate --不开启会提示：SQL Server blocked access to procedure 'sys.sp_OACreate' go sp_configure 'show advanced options', 1; go reconfigure; go sp_configure 'ole automation procedures', 1; go reconfigure; go drop function [regexReplace]go--创建函数 create function [dbo].[regexReplace] ( @source varchar(8000), --字符串 @regexp varchar(500), --正则表换式 @replace varchar(500), --替换值 @globalReplace bit = 0, --是否是全局替换 @ignoreCase bit = 0 --是否忽略大小写 ) returns varchar(1000) AS begin declare @hr int declare @objRegExp int declare @result varchar(5000) exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp output if @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp if @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace if @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase if @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OAMethod @objRegExp, 'Replace', @result output, @source, @replace if @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OADestroy @objRegExp if @hr <> 0 begin return null end /* 注释A -- while (charindex('..',@result)<>0) -- begin -- set @result=replace(@result,'..','.') -- end -- if(left(@result,1)='.') -- set @result=right(@result,len(@result)-1) -- if(right(@result,1)='.') -- set @result=left(@result,len(@result)-1) */ return @result end