CREATEprocedure[dbo].[proTableSelectPage] ( @Tablesvarchar(1000), --表名如testtable @PrimaryKeyvarchar(100), --表的主键,必须唯一性 @Sortvarchar(200) =NULL, --排序字段如f_Name asc或f_name desc(注意只能有一个排序字段) @CurrentPageint=1, --当前页 @PageSizeint=10, --每页大小 @Fieldsvarchar(1000) ='*', --显示的字段列表 @Filtervarchar(1000) =NULL, --条件语句,不加where,如 f_id>3 @Groupvarchar(1000) =NULL, --分组字段 @TotalPageint OutPut --返回总页数 ) --WITH ENCRYPTION ---加密存储 AS SET NOCOUNT ON Declare@intResultInt BeginTran DECLARE@sqlnvarchar(4000) If@Filterisnullor@Filter='' Begin --set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables set@Sql='select @intResult = count(*) from '+@Tables End Else Begin --set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter set@Sql='select @intResult = count(*) from '+@Tables+' where + '+@Filter End EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数 select@TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数 IF@SortISNULLor@Sort='' SET@Sort=@PrimaryKey DECLARE@SortTableVarChar(100) DECLARE@SortNameVarChar(100) DECLARE@strSortColumnVarChar(200) DECLARE@operatorChar(2) DECLARE@typeVarChar(100) DECLARE@precint IFCHARINDEX('DESC',@Sort)>0 BEGIN SET@strSortColumn=REPLACE(@Sort, 'DESC', '') SET@operator='<=' END ELSE Begin IFCHARINDEX('ASC', @Sort) >0 BEGIN SET@strSortColumn=REPLACE(@Sort, 'ASC', '') SET@operator='>=' END ELSE BEGIN SET@strSortColumn=@SORT SET@operator='>=' END End IFCHARINDEX('.', @strSortColumn) >0 BEGIN SET@SortTable=SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET@SortName=SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) +1, LEN(@strSortColumn)) END ELSE BEGIN SET@SortTable=@Tables SET@SortName=@strSortColumn END Select@type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype Where o.name =@SortTableAND c.name =@SortName IFCHARINDEX('char', @type) >0 Begin SET@type=@type+'('+CAST(@precASvarchar) +')' End DECLARE@strPageSizevarchar(50) DECLARE@strStartRowvarchar(50) DECLARE@strFiltervarchar(1000) DECLARE@strSimpleFiltervarchar(1000) DECLARE@strGroupvarchar(1000) IF@CurrentPage<1 Begin SET@CurrentPage=1 End SET@strPageSize=CAST(@PageSizeASvarchar(50)) SET@strStartRow=CAST(((@CurrentPage-1)*@PageSize+1) ASvarchar(50)) IF@FilterISNOTNULLAND@Filter!='' BEGIN SET@strFilter=' Where '+@Filter+'' SET@strSimpleFilter=' AND '+@Filter+'' END ELSE BEGIN SET@strSimpleFilter='' SET@strFilter='' END IF@GroupISNOTNULLAND@Group!='' Begin SET@strGroup=' GROUP BY '+@Group+'' End ELSE Begin SET@strGroup='' End set@sql='DECLARE @SortColumn '+@type+' SET ROWCOUNT '+@strStartRow+' Select @SortColumn='+@strSortColumn+' FROM '+@Tables+@strFilter+''+@strGroup+' orDER BY '+@Sort+' SET ROWCOUNT '+@strPageSize+' Select '+@Fields+' FROM '+@Tables+' Where '+@strSortColumn+@operator+' @SortColumn '+@strSimpleFilter+''+@strGroup+' orDER BY '+@Sort+' ' exec(@sql) print@sql If@@Error<>0 Begin RollBackTran Return-1 End Else Begin CommitTran Return@intResult---返回记录总数 End