两个分页存储过程的效率比较(国外英语资料).docVIP

两个分页存储过程的效率比较(国外英语资料) The efficiency of two paging stored procedures (both for multiple table queries) -- decryption stored procedures Exec sp_decryptp_page Use Newt8c8 Go If exists (select * from, dbo.sysobjects, where, name=p_page) Drop procedure p_page Go * / / * * paging storage process Create procedure p_page ( @Tables varchar (1000) -- table names can be more than one table, but cannot be aliased @PrimaryKey varchar (100) -- the primary key can be empty, but the value cannot be null when @Order is empty @Fields varchar (1000) = *, - query field (multiple tables), empty said select * @PageSize int = 15 -- per page record @CurrentPage int = 1, - the current page, which represents the first page @Filter varchar (1000) = null -- condition can be null without filling in where @Group, varchar (1000) = null, - grouping basis, can be empty, do not fill group by @Sort varchar (200) = null, - sort, defaults to ascending in the primary key, and does not fill in order by @TotalPage, int, output -- total pages ) With encryption - Encryption As Set NOCOUNT on Declare @intResult int Begin tran Declare @sql nvarchar (4000) If, @Filter, is, null, or, @Filter= Set @Sql =select, @intResult = count (` + @PrimaryKey + ) from + @Tables Else Set @Sql =select, @intResult = count (` + @PrimaryKey + ) from + @Tables + where + + @Filter Exec, sp_executesql, @sql, N@intResult, int, output, @intResult, output Select, @TotalPage=, ceiling ((@intResult+0.0) /@PageSize) If, @Sort, is, null, or, @Sort = Set @Sort = @PrimaryKey Declare @SortTable varchar (100) Declare @SortName varchar (100) Declare @strSortColumn varchar (200) Declare @operator char (2) Declare @type varchar (100) Declare @prec int If CHARINDEX (desc, @Sort) 0 Begin Set @strSortColumn = replace (@Sort,desc, ) Set @operator == End Else If CHARINDEX (asc, @Sort) 0 Begin Set @strSortColumn = replace (@Sort,asc, ) Set @operator == End Else Begin Set @strSortColumn = @SORT Set @operator == End If CHARINDEX (, @strSortColumn) 0