Analysis and code of the stored procedure for creating paging by SQL Server

Here we can see a Data Control paging function written in SQL stored procedures, regardless of the Data Control in the development tool, you only need to call this stored procedure to implement this method. It's easy. We no longer need to write a lot of SQL statements on every page. Misrosoft SQL Server is a data management database developed by Microsoft. The most popular databases in the world are Access, SQL Server, MySQl, and l databases, as long as you learn the syntax of a database, you will probably use other SQL servers. Now I have learned about SQL Server and I have been writing it here for a year, what have we learned? I asked myself and found that I did not learn anything. Apart from creating tables, executing simple SQL statements and writing simple stored procedures, have we ever written functions? Have you ever written SQL advanced stuff? Is a simple stored procedure written by ourselves? Do we still need to check the materials? If I write it by myself, I can't say ?????????, So there are still many things we have learned. Persistence is victory.

The following is a stored procedure that supports data paging. It is worth studying. Of course, we must take a serious look to learn a lot from it.

Create a stored procedure:

-- Obtain information about the total data on the page

Create procedure p_PageList

(

@ TableName nvarchar (300), -- Name of the table to be paged

@ PKey nvarchar (50), -- default primary key ID

@ FieldList nvarchar (500), -- the field to be searched

@ Condition nvarchar (1000), -- Condition, search criteria

@ OrderBy nvarchar (250), -- sort Order By ID

@ SQL nvarchar (1000), -- can be used or not used. The program can automatically generate SQL statements. Of course, you can only simply find the required information.

@ SqlGetRC nvarchar (1000), -- obtain the total SQL statement, or you can specify

@ CurrPage int, -- the current page you are viewing

@ PageSize int, -- the actual number of pages

@ RecordCount int, -- obtains the total number of data in the database. It can be passed or not passed.

@ Result int output -- output parameter

)

As

Declare @ PageCount int

If @ SqlGetRC = ''-- if the total data SQL statement is empty

Set @ SqlGetRC = 'select @ RecordCount = COUNT (0) from' + @ TableName + @ Condition

If @ RecordCount =-1 --

Begin

Exec sp_executesql @ SqlGetRC, n' @ RecordCount int out', @ RecordCount out

End

Set @ PageCount = (@ RecordCount + @ PageSize-1)/@ PageSize

If @ CurrPage> @ PageCount AND @ PageCount> 0

Set @ CurrPage = @ PageCount

 

If @ SQL =''

Begin

If @ PageSize = 0

Set @ PageSize = 10

If @ CurrPage = 1

Set @ SQL = 'select top '+ Cast (@ PageSize as nvarchar) + ''+ @ FieldList + 'from' + @ TableName + @ Condition +'' + @ OrderBy

Else

Set @ SQL = 'select top '+ Cast (@ PageSize as nvarchar) + ''+ @ FieldList + 'from' + @ TableName + 'where' + @ Pkey + 'not in (select top' + Cast (@ CurrPage-1) * @ PageSize as nvarchar) + ''+ @ Pkey + 'from' + +'' + @ Condition + ''+ @ OrderBy + ')' + replace (@ Condition, 'Where 1 = 1', '') +'' + @ OrderBy

End

Exec (@ SQL)

Select RecordCount = @ RecordCount, PageCount = @ PageCount

Set @ Result = 1

Knowledge Point summary:

1: Execute sp_executesql statements that can be repeatedly used or dynamically generated for multiple times. Transact-SQL statements or batch processing can contain embedded parameters.

Note: In terms of batch processing, name scope, and database context, sp_executesql has the same behavior as EXECUTE. The Transact-SQL statement or batch processing in the sp_executesql stmt parameter is compiled only when the sp_executesql statement is executed. Then, the content in stmt will be compiled and run as an execution plan. This execution plan is independent of the Execution Plan for batch processing called sp_executesql. Sp_executesql batch processing cannot reference the variable declared in the batch processing that calls sp_executesql. The local cursor or variable in sp_executesql batch processing is invisible to the batch processing that calls sp_executesql. Changes made to the database context are valid only before the end of the sp_executesql statement.

If only the parameter values in the statement are modified, sp_executesql can be used to execute the Transact-SQL statement multiple times instead of the stored procedure. Because the Transact-SQL statement remains unchanged and only the parameter value changes, the SQL Server Query Optimizer may reuse the execution plan generated during the first execution.

2: Cast explicitly converts a data type expression to another data type expression. CAST and CONVERT provide similar functions.

3: replace

Replace all instances of the second given string expression in the first string expression with the third expression.

Syntax

REPLACE ('String_expression1','String_expression2','String_expression3')

Parameters

'String_expression1'

The string expression to be searched.String_expression1The data type of the parameter can be implicitly convertedNvarcharOrNtext.

'String_expression2'

String expression to be searched.String_expression2The data type of the parameter can be implicitly convertedNvarcharOrNtext.

'String_expression3'

Replace the string expression.String_expression3The data type of the parameter can be implicitly convertedNvarcharOrNtext.

Return Value

NvarcharOrNtext

If any parameter is NULL, NULL is returned.

Example

The following example searches for "Anton" in the product name list and replaces it with "Anthony ":

	Copy code
Select replace (ProductName, 'anton ', 'Anthony') FROM Products