SQL Server Stored Procedure page, supporting CTE

Support Complex CTE statement calls:

Original SQL statement called:

With T (
Select ma_id from sa_affair_info where ad_id = 2203 and ai_affair_status = 2 and ai_is_pass = 'true'
),
Q
(
Select M. * From sa_main_affair M Join t on T. ma_id = M. ma_index_no
), P (
Select ai. * From sa_affair_info AI join Q on AI. ma_id = Q. ma_id where ai_affair_status = 2
)
Select * from P

 

Use Stored Procedure paging call to achieve the same effect (CTE supported ):
Declare @ return_value int,
@ Recordcount int,
@ Pagecount int

Exec @ return_value = [DBO]. [select_pagination_ex]
@ Tablename = n'p ',
@ Columns = n '*',
@ Currentpageindex = 1,
@ Pagesize = 10,
@ Recordcount = @ recordcount output,
@ Pagecount = @ pagecount output,
@ Orderbycolumns = n'ai _ id ',
@ Where = n '',
@ With = N' with T (
Select ma_id from sa_affair_info where ad_id = 2203 and ai_affair_status = 2 and ai_is_pass = 'true''
),
Q
(
Select M. * From sa_main_affair M Join t on T. ma_id = M. ma_index_no
), P (
Select ai. * From sa_affair_info AI join Q on AI. ma_id = Q. ma_id where ai_affair_status = 2
)'

Select @ recordcount as n' @ recordcount ',
@ Pagecount as n' @ pagecount'

Select 'Return value' = @ return_value

Go

Bytes -------------------------------------------------------------------------------------------------

Simple query statement call Method

Select * From sa_affair_info where ai_affair_status> 0 order by ai_id

The call statement is as follows:

Declare @ return_value int,
@ Recordcount int,
@ Pagecount int

Exec @ return_value = [DBO]. [select_pagination_ex]
@ Tablename = n' SA _ affair_info ',
@ Columns = n '*',
@ Currentpageindex = 1,
@ Pagesize = 10,
@ Recordcount = @ recordcount output,
@ Pagecount = @ pagecount output,
@ Orderbycolumns = n'ai _ id ',
@ Where = n'ai _ affair_status> 0 ',
@ With = n''

Select @ recordcount as n' @ recordcount ',
@ Pagecount as n' @ pagecount'

Select 'Return value' = @ return_value

Go

Bytes -------------------------------------------------------------------------------------------------

Paging stored procedure source code:

 

 

-- ===================================================== ========================================================== ====================
-- Single Table query call
--*************************************** **************************************** *********************
-- Original query statement
-- Select addressid, addressline1, city from [adventureworks]. [person]. [address] Where (2 = 2 or 3 = 3) and addressid> 3000
--*************************************** **************************************** *********************
-- Declare @ return_value int,
-- @ Pagecount int,
-- @ Recordcount int
-- Exec @ return_value = [DBO]. [select_pagination]
-- @ Tablename = n' [adventureworks]. [person]. [address] ',
-- @ Columns = n' SSID, addressline1, city ',
-- @ Currentpageindex = 1,
-- @ Pagesize = 10,
-- @ Recordcount = @ recordcount output,
-- @ Pagecount = @ pagecount output,
-- @ Orderbycolumns = n' ssid asc ',
-- @ Where = n' (2 = 2 or 3 = 3) and addressid> 100'
-- Select @ pagecount as n' @ pagecount'
-- Select @ recordcount as n' @ recordcount'
-- Select 'Return value' = @ return_value
-- Go
--*************************************** **************************************** ********
-- Connection query call
--*************************************** **************************************** *********************
-- Original query statement
-- Select [customerid], [territoryid], [accountnumber], [customertype], [rowguid], [modifieddate], customertype. [name]
-- From [adventureworks]. [sales]. [Customer] Join customertype on [sales]. [Customer]. customertype = customertype. ID
-- Order by [sales]. [Customer]. modifieddate DESC, [sales]. [Customer]. customerid DESC
--*************************************** **************************************** *********************
-- Use [adventureworks]
-- Go
-- Declare @ return_value int,
-- @ Pagecount int,
-- @ Recordcount int
-- Exec @ return_value = [DBO]. [select_pagination]
-- @ Tablename = n' [adventureworks]. [sales]. [Customer] Join customertype on [sales]. [Customer]. customertype = customertype. id ',
-- @ Columns = n' [mermerid], [territoryid], [accountnumber], [customertype], [rowguid], [modifieddate], customertype. [name] ',
-- @ Currentpageindex = 1916,
-- @ Pagesize = 10,
-- @ Recordcount = @ recordcount output,
-- @ Pagecount = @ pagecount output,
-- @ Orderbycolumns = n' [sales]. [Customer]. modifieddate DESC, [sales]. [Customer]. customerid DESC ',
-- @ Where = n''
-- Select @ pagecount as n' @ pagecount'
-- Select @ recordcount as n' @ recordcount'
-- Select 'Return value' = @ return_value
-- Go
-- ===================================================== ========================================================== ====================

Alter proc [DBO]. [select_pagination_ex] (
@ Tablename nvarchar (4000 ),
@ Columns nvarchar (4000 ),
@ Currentpageindex int,
@ Pagesize int,
@ Recordcount int output,
@ Pagecount int output,
@ Orderbycolumns nvarchar (1000 ),
@ Where nvarchar (4000 ),
@ With nvarchar (4000) -- defines a common expression,
)
Begin
Declare @ count_ SQL nvarchar (4000)
Declare @ parmdefinition nvarchar (1000)
Set @ parmdefinition = n' @ count int output ';
If @ with <> n''
Set @ count_ SQL = @ with + N' select @ COUNT = count (*) from '+ @ tablename + N' where 1 = 1'
Else
Set @ count_ SQL = n' select @ COUNT = count (*) from '+ @ tablename + N' where 1 = 1'

If @ where <> n''
Set @ count_ SQL = @ count_ SQL + N' and ('+ @ where + N ')'
-- Print @ count_ SQL
Execute sp_executesql @ count_ SQL, @ parmdefinition, @ COUNT = @ recordcount output;
If (@ recordcount % @ pagesize)> 0
Set @ pagecount = @ recordcount/@ pagesize + 1
Else
Set @ pagecount = @ recordcount/@ pagesize
Declare @ SQL nvarchar (4000)
If @ with <> n''
Begin
Set @ SQL = @
Set @ SQL = @ SQL + N', tmptable ('
End
Else
Set @ SQL = N' with tmptable ('
Set @ SQL = @ SQL + n'select' + @ columns + N', row_number () over (order'
Set @ SQL = @ SQL + @ orderbycolumns
Set @ SQL = @ SQL + N') rowno from'
Set @ SQL = @ SQL + @ tablename + N' where 1 = 1'
If @ where <> n''
Set @ SQL = @ SQL + N' and ('+ @ where + N ')'
Set @ SQL = @ SQL + N ')'
Declare @ beginno int
Declare @ endno int
Set @ beginno = (@ currentpageindex-1) * @ pagesize + 1
Set @ endno = @ beginno + @ pagesize-1
If @ endno> @ recordcount
Set @ endno = @ recordcount
Set @ SQL = @ SQL + N' select * From tmptable where rowno> = '+ convert (nvarchar (5), @ beginno)
+ N' and rowno <= '+ convert (nvarchar (5), @ endno)

Exec sp_executesql @ SQL
End