Publishing two large datasets common paging stored procedures

Publish two large data sets common paging stored procedures, welcome to contact me to explore ways to improve, and I will update the content in a timely manner posted at the following Web site
Http://www.thinksea.com/thinksea/show.aspx?id=92db4c46-2216-4b24-a72b-aa27fcf82e50

/*--a large data set paging program implemented with stored procedures

Show page x for specified tables, views, query results

Copy Right 2007.09 http://www.thinksea.com (please keep this information when propagating, thank you.) ）
Welcome to write to criticize, the author's email:thinksea@163.com qq:41464129

--Invoke the example
1. Simple Call Example:
EXEC selectpage "Book Table name", "[ID]", 5, 3, "book Number, book name", "book Number"
2, because to take into account the universality, so the query with the ordering of a certain requirements. If you sort the results first, you need to use the top percent. Examples are as follows:
EXEC selectpage "Select top percent * from Book table name order by book number", "[ID]", 5, 3, "book Number, book name", "book Number
*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N) [dbo].[ Selectpage] "") and OBJECTPROPERTY (ID, N "isprocedure") = 1)
drop procedure [dbo]. [Selectpage]
Go

CREATE PROCEDURE [dbo]. [Selectpage]
@QueryString nvarchar (4000),--table name, view name, query statement
@KeyFieldName nvarchar (250) = "",--primary key column, identity column, or column name with unique values
@PageIndex int=0,--Specifies a paging index, an integer starting at 0, and 0 represents the first page
@PageSize int=10--size per page (number of rows per page)
@FieldShow nvarchar (4000) = "",--the list of fields to display, * represents all the fields, and if the query results are marked with a literacy segment, you need to specify this value and do not include an identity field (because the query needs to create an identity field for the temporary table that would conflict with the Uniqueiden Tifier as an identity field except)
@FieldOrder nvarchar (1000) = ""/* sort Field List
Note: Because of the difference in the physical storage order of the data, if there are duplicate values in the sorted field that may cause the specified paging recordset to be returned invalid, you need to append a unique value field sort assist to complete the sorting function.
For example: There are two fields of ID (book number) and name (book name) in a book table, where the ID field is unique and the name field has duplicate values.
If you are reading data from a recordset sorted by name, you should consider using the Sort field list such as [name] ASC, [ID] ASC.
where [ID] ASC is used to assist the "[name] ASC" To complete the sort, and multiple records with the same name value that appear in the results sorted by name are sorted by ID
*/
As
declare @RecordsCount int,--Total records
@PagesCount INT--Total pages
IF IsNull (@KeyFieldName, "") = ""
BEGIN
RAISERROR ("must specify parameter @KeyFieldName", 16, 1)
Return
End
Set @KeyFieldName = "" + @KeyFieldName
Select @FieldShow =case isnull (@FieldShow, "") when "" then "*" Else "" + @FieldShow End
, @FieldOrder =case isnull (@FieldOrder, "") when "" Then "" Else "ORDER BY" + @FieldOrder End
, @QueryString =case when object_id (@QueryString) was not NULL then "+ @QueryString Else" ("+ @QueryS tring+ ") A" end

declare @sql nvarchar (4000)
Set @sql = "Select @RecordsCount =count (*) from" + @QueryString
EXEC sp_executesql @sql, N "@RecordsCount int output", @RecordsCount output--Total page Count
Set @PagesCount =ceiling ((@RecordsCount +0.0)/@PageSize)

declare @TempFieldName nvarchar (50)--Temporary field name
Set @TempFieldName = "[id_" +cast (NEWID () as varchar (40)) + "]"

Set @sql =n "SELECT top" +cast (@PageIndex + 1) * @PageSize as nvarchar) + @TempFieldName + N "=identity (int,1, 1), "+ @KeyFieldName +n" into the #tb1 from "+ @QueryString + @FieldOrder +n"
Select + @FieldShow +n "from" + @QueryString +n "where" + @KeyFieldName +n "in" (
Select Top ' +cast (@PageSize as nvarchar) + @KeyFieldName +n "from #tb1 where" + @TempFieldName + N ">" +cast (@PageIndex * @PageSize as nvarchar (10))
+n ")" + @FieldOrder
EXEC (@sql)
Select @RecordsCount as [Recordscount], @PagesCount as [Pagescount]
Go







/*--a large data set paging program implemented with stored procedures

Shows the n records from the beginning of article X of the specified table, view, and query results

Copy Right 2007.09 http://www.thinksea.com (please keep this information when propagating, thank you.) ）
Welcome to write to criticize, the author's email:thinksea@163.com qq:41464129

--Invoke the example
1. Simple Call Example:
EXEC selectrange "Book Table name", "[ID]", 5, 3, "book Number, book name", "book Number"
2, because to take into account the universality, so the query with the ordering of a certain requirements. If you sort the results first, you need to use the top percent. Examples are as follows:
EXEC selectrange "Select top percent * from Book table name order by book number", "[ID]", 5, 3, "book Number, book name", "figure Book Number "
*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N) [dbo].[ Selectrange] "") and OBJECTPROPERTY (ID, N "isprocedure") = 1)
drop procedure [dbo]. [Selectrange]
Go

CREATE PROCEDURE [dbo]. [Selectrange]
@QueryString nvarchar (4000),--table name, view name, query statement
@KeyFieldName nvarchar (250) = "",--primary key column, identity column, or column name with unique values
@BeginIndex int=0,--Specifies the starting index, an integer starting at 0, and 0 represents the first record
@Count int=10,--the maximum number of records (rows) to get, or ignore this condition if the value is NULL, and get all the records starting from the @BeginIndex. ）
@FieldShow nvarchar (4000) = "",--the list of fields to display, * represents all the fields, and if the query results are marked with a literacy segment, you need to specify this value and do not include an identity field (because the query needs to create an identity field for the temporary table that would conflict with the Uniqueiden Tifier as an identity field except)
@FieldOrder nvarchar (1000) = ""/* sort Field List
Note: Because of the difference in the physical storage order of the data, if there are duplicate values in the sorted field that may cause the specified paging recordset to be returned invalid, you need to append a unique value field sort assist to complete the sorting function.
For example: There are two fields of ID (book number) and name (book name) in a book table, where the ID field is unique and the name field has duplicate values.
If you are reading data from a recordset sorted by name, you should consider using the Sort field list such as [name] ASC, [ID] ASC.
where [ID] ASC is used to assist the "[name] ASC" To complete the sort, and multiple records with the same name value that appear in the results sorted by name are sorted by ID
*/
As
IF IsNull (@KeyFieldName, "") = ""
BEGIN
RAISERROR ("must specify parameter @KeyFieldName", 16, 1)
Return
End
Set @KeyFieldName = "" + @KeyFieldName
Select @FieldShow =case isnull (@FieldShow, "") when "" then "*" Else "" + @FieldShow End
, @FieldOrder =case isnull (@FieldOrder, "") when "" Then "" Else "ORDER BY" + @FieldOrder End
, @QueryString =case when object_id (@QueryString) was not NULL then "+ @QueryString Else" ("+ @QueryS tring+ ") A" end

declare @sql nvarchar (4000)
declare @TempFieldName nvarchar (50)--Temporary field name
Set @TempFieldName = "[id_" +cast (NEWID () as varchar (40)) + "]"

If @Count is not null
Set @sql =n "SELECT top" +cast (@BeginIndex + @Count as nvarchar ()) + @TempFieldName + N "=identity (int,1,1)," + @KeyFieldName +n "into the #tb1 from" + @QueryString + @FieldOrder +n "
Select + @FieldShow +n "from" + @QueryString +n "where" + @KeyFieldName +n "in" (
Select Top ' +cast (@Count as nvarchar) + @KeyFieldName +n "from #tb1 where" + @TempFieldName + N " > "+cast (@BeginIndex as nvarchar (10))
+n ")" + @FieldOrder
Else
Set @sql =n "SELECT" + @TempFieldName + N "=identity (int,1,1)," + @KeyFieldName +n "into #tb1 from" + @QueryStri ng+ @FieldOrder +n "
Select + @FieldShow +n "from" + @QueryString +n "where" + @KeyFieldName +n "in" (
Select "+ @KeyFieldName +n" from #tb1 where "+ @TempFieldName + N" > "+cast (@BeginIndex as nvarchar ( 10))
+n ")" + @FieldOrder
EXEC (@sql)
Go