动态SQL通用分页存储过程

/*
	存储过程名: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