一个分页procedure 实例:
获取指定表,指定查询条件的条数
- --create by veakou@126.com
- --20080607
- CREATE PROCEDURE [dbo].[U_C_GetTableRowCount]
- @TableName varchar(40), ----查询对象表名
- @ColumnName varchar(40) ='*', ----查询对象表列
- @Condition varchar(200) =' 1=1', ----查询对象(纯粹查询条件不包含where关键字)
- --@ReturnType bit =0, ----返回方式类型,0:select方式返回 1: output参数返回
- @Count int out ----查询结果返回值
- AS
- declare @sql nvarchar(4000)
- set @sql=N'select @Count=count('+@ColumnName+N') from ['+@TableName+N']'
- +case when @Condition=' 1=1' then '' else N' where '+@Condition end
- exec sp_executesql @sql,
- N'@Count int out',
- @Count out
- GO
分页存储过程:
- --create by Veakou@126.com
- --2008-6-10
- CREATE PROCEDURE [dbo].[U_C_Page]
- @TableName varchar(40), ----查询表名
- @ColumnsName nvarchar(200) =' * ', ----查询字段名(column1,column2......)
- @PKColumn varchar(20), ----主键字段
- @Condition nvarchar(1000) =' 1=1 ', ----查询条件
- @OrderType bit =0, ----排序类型 0:asc 1:desc
- @OrderColumn varchar(20), ----排序字段
- @PageSize int =20, ----页面尺寸
- @CurrentPage int =1 ----当前页
- AS
- Begin
- declare @strSql nvarchar(4000) ----sql字串
- declare @strTmp nvarchar(200) ----临时变量
- declare @orderStr nvarchar(200) ----排序字串
- Begin
- if @OrderType = 0
- Begin
- set @orderStr = ' order by '+@OrderColumn+' asc'
- set @strTmp = '>(select max('
- End
- else
- Begin
- set @orderStr = ' order by '+@OrderColumn+' desc'
- set @strTmp = '<(select min('
- End
- End
- if @CurrentPage = 1 ----第一页
- Begin
- set @strSql = 'select top '+str(@PageSize)+' '+@ColumnsName+' from '+@TableName+' where '+@Condition+@orderStr
- End
- else ----第N页
- Begin
- set @strSql = 'select top '+str(@PageSize)+' '+@ColumnsName+' from '+@TableName+' where '+@Condition+ ' and '+@PKColumn+@strTmp+@PKColumn+') from
- (select top '+str((@CurrentPage-1)*@PageSize)+' '+@PKColumn+' from '+@TableName+' where '+@Condition+@orderStr+') as tmp)'+@orderStr
- End /**/
- Exec(@strSql)
- End
- GO