set @cmdstr='select top ' set @cmdstr=@cmdstr+convert(nvarchar,@PageSize) if @Order='DESC' and @PageIndex>1 set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'<' else if @PageIndex=1 set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>=' else set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>' if @PageIndex>1 begin if @Order='ASC' set @cmdstr=@cmdstr+'(select max ('+@OKey+') from (select top ' else set @cmdstr=@cmdstr+'(select min ('+@OKey+') from (select top ' set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize) set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+' order by '+@OKey+' '+@Order+') as t) ' end else set @cmdstr=@cmdstr+'0 ' if @Where<>'' set @cmdstr=(@cmdstr+' and '+@Where+' order by '+@OKey+' '+@Order) else set @cmdstr=(@cmdstr+'order by '+@OKey+' '+@Order)
比如
SELECT @RecordCount = COUNT(user) FROM #temp
就是返回总的记录数
@RecordCount可以作为存储过程的OUT参数
然后直接帮定到dalalist 或者 datagrid
使用SQL参数out返回类型string!!!如果使用返回列 得到 记录总数
SELECT COUNT(user) FROM #temp
那就一定要调用该过程2次
得到总数cmd.ExecuteScalar()
分页还要调用一次
难道就没有一种方法 通用 吗
--功能:通过数据库翻页
--设计:王文涛CREATE PROCEDURE Ture_Page @PageSize int, --每页的行数
@PageIndex int, --1 代表第一页
@Col varchar(200), --要显示的字段
@Table varchar(200), --所用到的表
@Where varchar(200)='', --所用到的条件
@OKey varchar(50), --排序字段
@Order varchar(20)='ASC' --排序方式as
declare @cmdstr varchar(2000)
set nocount on
set @cmdstr='select top '
set @cmdstr=@cmdstr+convert(nvarchar,@PageSize)
if @Order='DESC' and @PageIndex>1
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'<'
else if @PageIndex=1
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>='
else
set @cmdstr=@cmdstr+' '+@Col+' from '+@Table+' where '+@OKey+'>'
if @PageIndex>1
begin
if @Order='ASC'
set @cmdstr=@cmdstr+'(select max ('+@OKey+') from (select top '
else
set @cmdstr=@cmdstr+'(select min ('+@OKey+') from (select top '
set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize)
set @cmdstr=@cmdstr+' '+@OKey+' from '+@Table+' order by '+@OKey+' '+@Order+') as t) '
end
else
set @cmdstr=@cmdstr+'0 '
if @Where<>''
set @cmdstr=(@cmdstr+' and '+@Where+' order by '+@OKey+' '+@Order)
else
set @cmdstr=(@cmdstr+'order by '+@OKey+' '+@Order)
print @cmdstr
exec(@cmdstr)
set nocount off
GO