--如果@RecordCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@RecordCount赋值) IF@RecordCountISNULL BEGIN DECLARE@sqlnvarchar(4000) SET@sql=N'SELECT @RecordCount=COUNT(*)' +N' FROM '+@tbname +N''+@WhereString EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT END
--第一页直接显示 IF@PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N''+@FieldShow +N' FROM '+@tbname +N''+@WhereString +N''+@FieldOrder) ELSE BEGIN --处理别名 IF@FieldShow=N'*' SET@FieldShow=N'a.*'
--生成主键(惟一键)处理条件 DECLARE@Where1nvarchar(4000),@Where2nvarchar(4000), @snvarchar(1000),@Field sysname SELECT@Where1=N'',@Where2=N'',@s=@FieldKey WHILECHARINDEX(N',',@s)>0 SELECT@Field=LEFT(@s,CHARINDEX(N',',@s)-1), @s=STUFF(@s,1,CHARINDEX(N',',@s),N''), @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field, @Where2=@Where2+N' AND b.'+@Field+N' IS NULL', @WhereString=REPLACE(@WhereString,@Field,N'a.'+@Field), @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field), @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field) SELECT@WhereString=REPLACE(@WhereString,@s,N'a.'+@s), @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s), @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s), @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''), @Where2=CASE WHEN@WhereString=''THEN N'WHERE (' ELSE@WhereString+N' AND (' END+N'b.'+@s+N' IS NULL'+@Where2+N')'
--执行查询 EXEC(N'SELECT TOP '+@TopN +N''+@FieldShow +N' FROM '+@tbname +N' a LEFT JOIN(SELECT TOP '+@TopN1 +N''+@FieldKey +N' FROM '+@tbname +N' a '+@WhereString +N''+@FieldOrder +N')b ON '+@Where1 +N''+@Where2 +N''+@FieldOrder) END
--处理开始点和结束点 Declare@StartRecordint; Declare@EndRecordint; Declare@TotalCountSqlnvarchar(500); Declare@SqlStringnvarchar(2000); set@StartRecord= (@PageIndex-1)*@PageSize+1 set@EndRecord=@StartRecord+@PageSize-1 SET@TotalCountSql= N'select @TotalRecord = count(*) from '+@TableName;--总记录数语句 SET@SqlString= N'(select row_number() over (order by '+@OrderString+') as rowId,'+@ReFieldsStr+' from '+@TableName;--查询语句 -- IF (@WhereString! =''or@WhereString!=null) BEGIN SET@TotalCountSql=@TotalCountSql+' where '+@WhereString; SET@SqlString=@SqlString+' where '+@WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set@SqlString='select * from '+@SqlString+') as t where rowId between '+ltrim(str(@StartRecord)) +' and '+ltrim(str(@EndRecord)); Exec(@SqlString) END