日期:2014-05-18 浏览次数:20527 次
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=0 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