Asp.net paging Stored Procedure instance analysis experience

1. Start to restore the original paging stored procedure.
Copy codeThe Code is as follows:
Alter procedure [dbo]. [sp_ SQL _Paging]
(
@ SqlDataTable NVARCHAR (4000), -- table name
@ PrimaryKey NVARCHAR (4000), -- primary key name
@ Fields NVARCHAR (4000), -- the field to be returned
@ PageSize INT, -- page size
@ PageIndex INT, -- page number
@ RecordCount int output, -- total number of records
@ StrOrderBy NVARCHAR (4000), -- Sort
@ StrWhere NVARCHAR (4000) -- Query Condition
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ strSQL1 NVARCHAR (4000) -- SQL statement 1
DECLARE @ strSQL2 NVARCHAR (4000) -- SQL statement 2

-- Create a temporary table
-- Used to save the table number and primary key
Create table # Temp_Paging
(
Temp_Paging_Id INT,
RowNumber INT
)
SET @ strSQL1 = 'insert INTO [# Temp_Paging] (Temp_Paging_Id, RowNumber) select' + @ PrimaryKey + ', ROW_NUMBER () OVER (' + @ strOrderBy + ') AS RowNumber FROM '+ @ SqlDataTable + ''+ @ strWhere
EXEC SP_EXECUTESQL @ strSQL1
SET @ recordCount = @ ROWCOUNT -- get the total number of records

-- Determine the page index
IF @ pageIndex> @ recordCount * 1.0/@ pageSize + 1.0 OR @ recordCount <= @ pageSize
BEGIN
SET @ pageIndex = 1
END

-- Paging Query
SET @ strSQL2 = 'select' + @ Fields + 'from' + @ sqlable able + 'where' + @ PrimaryKey + 'IN (SELECT Temp_Paging_Id FROM [# Temp_Paging] WHERE RowNumber BETWEEN' + str (@ pageIndex-1) * @ pageSize + 1) + 'and' + Str (@ pageIndex * @ pageSize) + ')' + @ strOrderBy
EXEC SP_EXECUTESQL @ strSQL2
Drop table # Temp_Paging -- delete a temporary TABLE
END

It is easy to see from the original paging storage process that the temporary table Storage number is used here, and then calculated by pageIndex and pageSize, paging is performed.

Because the primary key is used as the query condition, the primary key value is also saved in the temporary table.

Obviously, the temporary table here cannot be universal, because the primary key type is not necessarily the INT type defined above, or it can be another type, such as uniqueidentifier (globally unique identifier ).

In this case, the stored procedure encountered a problem and must be improved.

2. Train of Thought 1
The idea is simple. Declare this type as a variable, and then use the system table to obtain the primary key type of the table and assign it to the variable. It looks wonderful. Try it first.

We can insert the following code:
Copy codeThe Code is as follows:
DECLARE @ colType NVARCHAR (50) -- primary key column type
DECLARE @ strSQL3 NVARCHAR (500) -- create a temporary TABLE statement

SET @ colType = (SELECT typ. name as Data Type
FROM sys. columns col
Left join sys. types typ
On (col. system_type_id = typ. system_type_id AND col. user_type_id = typ. user_type_id)
WHERE col. object_id = (SELECT object_id FROM sys. tables WHERE name = @ SqlDataTable)
And exists
(SELECT 1 FROM sys. indexes idx
Join sys. index_columns idxCol
On (idx. object_id = idxCol. object_id)
WHERE idx. object_id = col. object_id
AND idxCol. index_column_id = col. column_id
AND idx. is_primary_key = 1
))

SET @ strSQL3 = 'create TABLE # Temp_Paging
(
Temp_Paging_Id '+ @ colType + ',
RowNumber INT
)'
PRINT @ strSQL3
-- EXEC (@ strSQL3)

Print result:
Copy codeThe Code is as follows:
Create table # Temp_Paging
(
Temp_Paging_Id uniqueidentifier,
RowNumber INT
)

Obviously, we have obtained the required temporary table. At this time, I was very happy because I got what I wanted. But it seems that it is not over yet. I want to execute this stored procedure.

Bad results often appear at this moment:
This is a strange thing. It is clear that the printed statements are correct to create a temporary table, and they are also executed. Why is the subsequent operations on the temporary table invalid?

Ask your colleagues for information and finally understand that the original temporary table is divided into local temporary tables and global temporary tables. Pay attention to the actual deletion time of the local temporary table.

To put it simply, when using EXEC (@ strSQL3) to create a temporary table, the temporary table has been deleted. Because the session in the EXEC process has ended, the temporary table is deleted.

Here is a blog post that gives a more detailed explanation. For details, refer to: SQL temporary tables.

3. Train of Thought 2

The above idea does not seem to work, so we should try another way of thinking. Instead of creating a temporary table, we should use the idea of Select * from (select * from table) as temptable instead of creating a temporary table.

The Code is as follows:
Copy codeThe Code is as follows:
Alter procedure [dbo]. [sp_ SQL _Paging]
(
@ SqlDataTable NVARCHAR (4000), -- table name
@ PrimaryKey NVARCHAR (4000), -- primary key name
@ Fields NVARCHAR (4000), -- the field to be returned
@ PageSize INT, -- page size
@ PageIndex INT, -- page number
@ RecordCount int output, -- total number of records
@ StrOrderBy NVARCHAR (4000), -- Sort
@ StrWhere NVARCHAR (4000) -- Query Condition
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ strSQL1 NVARCHAR (4000) -- SQL statement 1
DECLARE @ strSQL2 NVARCHAR (4000) -- SQL statement 2
DECLARE @ strSQL3 NVARCHAR (4000) -- SQL statement 3

SET @ strSQL1 = 'select' + @ PrimaryKey + ', ROW_NUMBER () OVER (' + @ strOrderBy + ') AS RowNumber FROM' + @ SqlDataTable + ''+ @ strWhere

-- Get the total number of records
SET @ strSQL3 = 'select @ recordCount = COUNT (*) FROM '+ @ SqlDataTable + ''+ @ strWhere
EXEC SP_EXECUTESQL
@ Stmt = @ strSQL3,
@ Params = n' @ recordCount as int output ',
@ RecordCount = @ recordCount OUTPUT

-- Paging Query
IF @ pageIndex> @ recordCount * 1.0/@ pageSize + 1.0 OR @ recordCount <= @ pageSize
BEGIN
SET @ pageIndex = 1
END
SET @ strSQL2 = 'select' + @ Fields + 'from' + @ SqlDataTable + 'where' + @ PrimaryKey + 'in (SELECT '+ @ PrimaryKey + 'from (' + @ strSQL1 + ') tempTable WHERE RowNumber BETWEEN '+ Str (@ pageIndex-1) * @ pageSize + 1) +' AND '+ Str (@ pageIndex * @ pageSize) +') '+ @ strOrderBy
EXEC SP_EXECUTESQL @ strSQL2
END

Here is a small point of knowledge. Pay attention to the EXEC SP_EXECUTESQL statement and the difference between EXEC (@ strsql) and EXEC (@ strsql. For more information, see.

This is the case for paging.