/*
存储过程名:proc_GetByPage
功能:通用分页显示查询
输入参数:
@tblName:表名或者视图名
@FieldKey:用于定位记录的主键(惟一键)字段
@strGetFields: 以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@PageSize: 页尺寸
@PageIndex: 页码
@doCount: 返回记录总数, 非0 值则返回
@strOrderBy: 排序字段信息,(注意: 不要加ORDER BY) 格式: Field1 DESC, Field2 ASC
@strWhere: 查询条件
输出参数:@RecordCount: 记录总数
*/
--DECLARE @RecordCount int
--EXEC proc_GetByPage 'tb_EntMemberMsg','EMMID','EMMID,EMMTID',15,2,1,'EMMTID desc,EMMID desc','where emmid <1000',@RecordCount
if OBJECT_ID('proc_GetByPage') is not null
drop proc proc_GetByPage
go
create proc proc_GetByPage
@tblName varchar(255),
@FieldKey varchar(100),
@strGetFields varchar(1000) = '*',
@PageSize int = 10,
@PageIndex int = 1,
@doCount bit = 0,
@strOrderBy varchar(500) = '',
@strWhere varchar(1500) = '',
@RecordCount int output
AS
DECLARE @strSQL varchar(5000) SET @strSQL = '' -- 主语句
DECLARE @strFieldKey varchar(100) --主键
IF @FieldKey ='' OR len(@FieldKey)<0
SET @strFieldKey='*'
ELSE
SET @strFieldKey=@FieldKey
SET @RecordCount = 0
/*--统计总数方法:*/
IF (@doCount != 0) --如果@doCount传递过来的非0,就执行总数统计
BEGIN
declare @TmpSelect NVarchar(600)
set @TmpSelect = 'select @RecordCount = COUNT('+@strFieldKey+') FROM [' + @tblName + '] ' + @strWhere
execute sp_executesql @TmpSelect, N'@RecordCount int OUTPUT' , @RecordCount OUTPUT
END
PRINT @RecordCount
IF @strGetFields ='' OR len(@strGetFields)<0
SET @strGetFields='*'
SET @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrderBy+
') AS SysIndex,'+@strGetFields+' FROM '+@tblName+' '+@strWhere+' )AS temp WHERE SysIndex BETWEEN '+
cast(@PageSize*(@PageIndex-1)+1 AS varchar(20))+' AND '+cast(@PageSize*@PageIndex AS varchar(20))+' ORDER BY '+@strOrderBy
--PRINT @strSQL
EXEC (@strSQL) --执行分页查询
Go
动态SQL通用分页存储过程
最新推荐文章于 2020-09-03 17:20:16 发布