Use [Reportservertempdb]gocreate PROCEDURE [dbo]. [Seachtablepage] (@TableName varchar,--table name @fileds varchar,--query field @orderfiled varchar,--sort field @isdesc BIT,-- Whether to sort in descending order @wherestring VARCHAR (2000),--query field @pageindex int,--current page @pagesize int,--number of bars per page @totalrecord int output--return total number of bars) ASBE Gindeclare @OrderString VARCHAR (@PageIndex is NULL OR @PageIndex <= 0) Beginset @PageIndex = 1ENDIF (@PageSize is NULL OR @PageSize <= 0) Beginset @PageSize = 10ENDDECLARE @StartRowID intdeclare @EndRowID intset @StartRowID = (@PageIn DEX-1) * @PageSize + 1SET @EndRowID = @PageIndex * @PageSizeIF (@WhereString is null OR @WhereString = ") BEGIN SET @Whe restring = ' 1 = 1 ' ENDIF (@OrderFiled is null OR @OrderFiled = ") beginset @OrderFiled = ' createdate ' ENDIF (@IsDesc is null OR @IsDesc = 1) beginset @OrderString = @OrderFiled + ' DESC ' endelsebeginset @OrderString = @OrderFiled + ' ASC ' Enddeclare @TotalSQL NVARCHAR SET @TotalSQL = ' SELECT @Total = COUNT (*) from ' + @TableName + ' WHERE ' + @WhereString + ' EXEC sp_executesql @TotalSQL, N ' @Total BIGINT out ', @TotalRecord OUTPUT--Returns the total number of records declare @SelectSQ L NVARCHAR IF (@TotalRecord <= @PageSize and @PageIndex = 1) beginset @SelectSQL = ' SELECT ' + @Fileds + ' from ' + @TableName + ' WHERE ' + @WhereString + ' ORDER by ' + @OrderStringENDELSEBEGINSET @SelectSQL = ' SELECT row_number () over (ORDER by ' + @OrderString + ') As RowId, ' + @Fileds + ' from ' + @TableName + ' WHERE ' + @WhereStringSET @SelectSQL = ' SELECT * FROM (' + @SelectSQL + ') As tab WHERE RowId between ' + LTrim (str (@StartRowID)) + ' and ' + LTrim (str (@EndRowID)) + ' EndPrint @SelectSQLEXEC (@S Electsql) END
SQL Server Paging stored procedures