SQL paging Universal Stored Procedures

Use [Db]go/** * * * object:storedprocedure [dbo].    [Sp_aspnetpager] Script date:10/23/2015 16:37:33 * * * * **/SET ansi_nulls ongoset quoted_identifier ongoalter PROCEDURE [dbo]. [Sp_aspnetpager] @PageSize INT=Ten, --page size @PageIndex INT=1, --page number @TableName VARCHAR (255) , --table name @KeyFields VARCHAR (255) ="', --the sorted field name @ShowFields VARCHAR ( +) ='*', --columns that need to be returned @OrderType BIT=0,--Set the sort type, non-0the value is descending @WhereString VARCHAR ( the) ="',--Query criteria (note: Do not addwhere) @OrderFields VARCHAR ( +)="',--a comma-delimited list of sorted fields that you can specify after a field desc/ASC @RecordCountintOUTPUT--Total pages as DECLARE @StrSQL VARCHAR ( the)       --The subject sentence DECLARE @StrTmp VARCHAR ( the)        --temporary variable DECLARE @StrOrder VARCHAR ( -)        --Sort Type IF @RecordCount is NULL BEGIN DECLARE @sql nvarchar (4000) SET @sql=n'SELECT @RecordCount =count (*)'+n' from'+@TableName+n'WHERE'+@WhereString EXEC sp_executesql @sql, N'@RecordCount int OUTPUT', @RecordCount OUTPUT end--The above code means that if the @docount pass over 0, the total number of executions is counted. All of the following codes are in the case of a @docount of 0 IF @OrderType!=0BEGIN SET @StrTmp='< (select min'SET @StrOrder='ORDER BY ['+ @KeyFields +'] Desc'--if @ordertype is not 0, it is important to perform descending order. END ELSE BEGIN SET @StrTmp='> (SELECT Max'SET @StrOrder='ORDER BY ['+ @KeyFields +'] ASC'END IF @PageIndex=1BEGIN IF @WhereString!="'SET @StrSQL='Select Top'+ STR (@PageSize) +' '+ @ShowFields +'From ['+@TableName+'] where'+ @WhereString +' '+@StrOrder ELSE SET @StrSQL='Select Top'+ STR (@PageSize) +' '+ @ShowFields +'From ['+ @TableName +'] '+@StrOrder--If the above code is executed on the first page, this will speed up the execution and END ELSE BEGIN--The following code gives @strsql the SQL code SET to actually execute @StrSQL='Select Top'+ STR (@PageSize) +' '+ @ShowFields +'From ['+ @TableName +'] WHERE ['+ @KeyFields +']'+ @StrTmp +'(['+@KeyFields+']) from (select Top'+ STR (@PageIndex-1 )                                           * @PageSize) +' ['+ @KeyFields +'] FROM ['+ @TableName +']'+@StrOrder+') as Tbltmp)'+@StrOrder IF @WhereString!="'SET @StrSQL='Select Top'+ STR (@PageSize) +' '+ @ShowFields +'From ['+@TableName+'] WHERE ['+ @KeyFields +']'+ @StrTmp +'(['+ @KeyFields +']) from (select Top'+ STR (@PageIndex-1) * @PageSize) +' ['+ @KeyFields +'] FROM ['+ @TableName +'] where'+ @WhereString +' '+@StrOrder+') as Tbltmp) and'+ @WhereString +' '+@StrOrder END EXEC (@StrSQL)

SQL paging Universal Stored Procedures