Analysis of paging Stored Procedure instances (1)

I recently modified a paging storage process. As a newbie, I still benefited a lot from it. Now I will start my paging journey today.

1. Start Restoration

Next, let's take a look at the original paging stored procedure.

 
 

  
  
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:

 
 

  
  
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:

 
 

  
  
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.