Dv_GetRecordFromPage

CREATE PROCEDURE [dbo].[Dv_GetRecordFromPage]

	@tableName   nvarchar(100),-- 表名
	@fldSort nvarchar(100) = 'dateandtime',-- 排序字段
	@pageSize int = 30,-- 每页大小 
	@page  int = 1 ,-- 第几页
	@Sort  bit = 1,-- 排序,@Sort=0  升序@Sort=1  降序
	@ConditionStr nvarchar(1000),--查询条件
	@fldName  nvarchar(2000) = '*'--查询字段
	AS
	Declare @strTmp nvarchar(4000)
	Declare @sqlSort nvarchar(20)
	Declare @Compare varchar(1)
	Declare @Compare1 nvarchar(20)
	Declare @intCounts int
	DECLARE @var_Splitvalue varchar(5000)
	DECLARE @nRet int

	If @Sort=1
		Begin
		Set @sqlSort ='DESC'
		Set @Compare = '<'
		Set @Compare1='Min'
		End
	Else
		Begin
		Set @sqlSort ='ASC'
		Set @Compare = '>'
		Set @Compare1='Max'
	End
	If @page >1
		begin

			SELECT @intCounts=(@Page-1) * @pagesize

			SET @strTmp='SELECT @var_Splitvalue ='+@Compare1+'(' + @fldSort + ') FROM (Select Top '+CAST(@intCounts as nvarchar)+' ' + @fldSort + ' From '+@tableName+'  where '+@ConditionStr+' order BY ' + @fldSort + ' ' + @sqlSort+') as t'

			EXEC sp_executesql @strTmp,N'@var_Splitvalue varchar(5000) output',@var_Splitvalue output

			SET ROWCOUNT @pagesize

			SET @strTmp='SELECT '+ @fldName +' FROM '+@tableName+' where '+ @ConditionStr +' AND ' + @fldSort+ @Compare + ' @2 '+' '+' ORDER BY ' + @fldSort + ' ' + @sqlSort

			EXEC sp_executesql @strTmp,N'@2 varchar(5000)',@2=@var_Splitvalue

		End
	Else
		begin
			SET ROWCOUNT @pagesize

			SET @strTmp='SELECT '+ @fldName +' FROM '+@tableName+'  where ' +@ConditionStr+' ORDER BY ' + @fldSort + ' ' + @sqlSort

			EXEC sp_executesql @strTmp
		end