How to use stored procedures to realize single conditional pagination in SQLServer

SQL Server how to use stored procedures to achieve single conditional paging, for this problem, this article details the corresponding analysis and solution, hoping to help more want to solve this problem of small partners to find a simpler and easier way.

SQLServer Procedure Pagination_basic：ALTER PROCEDURE [qiancheng]. [Pagination_basic] (@Table_name VARCHAR (255),--name of table@Rows_target VARCHAR (1000) = '*',--search rows @Rows_condition VARCHAR (1000) = '',--the condition to find target (no where)@Rows_order VARCHAR (255) = '',--the rows to rank@Order_type INT = 0,-- *Q*C* 0 normal 1 down@PageSizes INT = 10,--the size of each page@PageIndex INT = 1,--current page@ShowPages INT,--whether show the pages *Q*C* 1-yes 0-no@ShowRecords INT,--whether show the record *Q*C* 1-yes 0-no@Records_total INT OUTPUT,--returned total records@Pages_total INT OUTPUT --returned total pages) ASDECLARE @MainSQL_QC nvarchar (2000) --Main SQL sentenceDECLARE @Var_QC VARCHAR (100) --Temporary variateDECLARE @Order_QC VARCHAR (400) --the sort to rankSET @Records_total = 0SET @Pages_total = 0IF @ShowRecords = 1OR @ShowPages = 1BEGINIF @Rows_condition != ''SET @MainSQL_QC = 'select @Records_total = count(1) from [' + @Table_name + '] where ' +@Rows_conditionELSESET @MainSQL_QC = 'select @Records_total = count(1) from [' + @Table_name + ']' EXEC sp_executesql @MainSQL_QC, N'@Records_total int out' ,@Records_total OUTPUTENDIF @ShowPages = 1BEGINIF @Records_total 0SET @Pages_total = @Pages_total + 1ENDENDIF @Order_type = 1BEGINSET @Var_QC = '(select max'SET @Order_QC = ' order by [' + @Rows_order + '] asc'ENDIF @PageIndex = 1BEGINIF @Rows_condition != ''SET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] where ' + @Rows_condition + ' ' + @Order_QCELSESET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] ' + @Order_QCENDELSEBEGINIF @Rows_condition != ''SET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] where [' + @Rows_order + ']' + @Var_QC + '([' + @Rows_order + ']) from (select top ' + str((@PageIndex - 1) *@PageSizes) + ' [' + @Rows_order + '] from [' + @Table_name + '] where ' + @Rows_condition + ' ' + @Order_QC + ') as Tmep_QC) and ' + @Rows_condition + ' ' + @Order_QCELSESET @MainSQL_QC = 'select top ' + str(@PageSizes) + ' ' +@Rows_target + ' from [' + @Table_name + '] where [' + @Rows_order + ']' + @Var_QC + '([' + @Rows_order + ']) from (select top ' + str((@PageIndex - 1) *@PageSizes) + ' [' + @Rows_order + '] from [' + @Table_name + ']' + @Order_QC + ') as Tmep_QC)' + @Order_QCEND EXEC (@MainSQL_QC)

Call: execute pagination_basic 'UserDetail','*',' id','1',' 5','1',' 1','1',' 1','',''

Mainly the last sentence, split down is like this:

select top column name per page from [table name] where [sorting field name] < --1 Output in reverse order If the column is less than the minimum number of previous pages

(select min ( [sort field name] )from --2 Gets the minimum value of a specified column name and outputs it

(select top (current page-1)* per page [sort field name] from [table name] where [condition] [sort type]) --3 select previous page total data output in reverse order

as Tmep_QC)--4 Create a temporary table named Tmep_QC--2 Get the minimum value of a specified column name and output

and [Condition] [Sort Type]--1 Output in reverse order If the column is less than the minimum number of previous pages

About SQL Server how to use stored procedures to achieve single-condition paging questions to share the answer here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.