1. Start the restore below first look at the original paging stored procedures.
Copy Code code 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 conditions
)
As
BEGIN
SET NOCOUNT on
DECLARE @strSQL1 NVARCHAR (4000)--SQL statement 1
DECLARE @strSQL2 NVARCHAR (4000)--SQL statement 2
--Create a temporary table
--to save the number of the table and the 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 total number of records
--Determine 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 ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' in (select temp_paging_id from [#Temp_Paging] WHERE rownumber BETWEEN ' + str ((@pageIndex-1) * @pageSize + 1) + ' and ' + str (@pageIndex * @pageSize) + ') ' + @strOrd Erby
EXEC sp_executesql @strSQL2
DROP table #Temp_Paging--Delete temporary tables
End
It is easy to see from the original paging stored procedure that a temporary table is used to save the number, and then the page is paginated through pageindex and pagesize calculations.
Because the primary key is also used as the query condition, the primary key value is also saved in the temporary table.
Obviously, the temporary table here is not universal because the type of the primary key is not necessarily the int type defined above, it can be other types, such as: uniqueidentifier (globally unique identifier).
In this case, the stored procedure has a problem, so it has to be improved.
2. The idea of a
Thinking a very simple, then declare this type as a variable, and then through the system table to get the table's primary key type, and then assign to the variable is OK. It looks wonderful, so try it first.
We can insert the following code:
Copy Code code 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 results:
Copy Code code as follows:
CREATE TABLE #Temp_Paging
(
temp_paging_id uniqueidentifier,
RowNumber INT
)
Obviously we got the temporary table we needed. Then I was happy because I got what I wanted. But it doesn't seem to be over yet, I'm going to execute the stored procedure.
Bad results often occur at this time:
Here is a strange thing, according to the printed clearly is correct to create a temporary table statement, but also executed, why the next operation of the temporary table is invalid?
Find information to ask colleagues, finally understand, the original temporary table is divided into local temporary tables and global temporary tables. Local temporary tables need to be aware of the actual deletion time.
Here's a simple point: when you create a temporary table with exec (@strSQL3), you have deleted the temporary table. Because the session of the exec process has ended, the temporary table is deleted.
Here is a blog to do a more specific explanation, you can refer to: Drip in the Heart _sql temporary table
3. Two ideas
The above idea does not seem to work, that is better to change the idea, simply do not create a temporary table, with SELECT * FROM (SELECT *) as temptable this idea in place of creating temporary tables.
The code is as follows:
Copy Code code 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 conditions
)
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 + ' FRO M (' + @strSQL1 + ') temptable WHERE rownumber BETWEEN ' + str ((@pageIndex-1) * @pageSize + 1) + ' and ' + str (@pageIndex * @pageSize) + ') ' + @strOrderBy
EXEC sp_executesql @strSQL2
End
Here's a little bit of knowledge, pay attention to exec Sp_executesql's writing and the difference with exec (@strsql). We can go to find the information about.
Some of the things about pagination are written here, for reference only.
