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

获取记录条数：
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
–print @sql
exec sp_executesql @sql,
N’@Count int out’,
@Count out
GO