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
动网论坛里面用到的分页存储过程Dv_GetRecordFromPage
最新推荐文章于 2022-10-10 13:53:57 发布