If (object_id (N 'syspr _ tablepaginationsearch', N 'P') is not null)
Drop procedure dbo. syspr_TablePaginationSearch
Go
Create procedure syspr_TablePaginationSearch
(
@ SelectField nvarchar (512) = '*' -- Select statement Field. Do not enter "select" when calling"
, @ FormTables nvarchar (512) -- Form clause, table name, including schema name. Do not enter "from" when calling"
, @ WhereField nvarchar (512) = null -- Where statement, judgment field, do not enter "where" when calling"
-----------------------------------------------------
, @ OverPartitionField nvarchar (512) = null -- Partition field,
, @ OverOrderField nvarchar (512) -- paging field, which is the main sorting field and must be
, @ GroupField nvarchar (512) = null -- Group Field
, @ HavingField nvarchar (512) = null -- grouping condition
, @ OrderField nvarchar (512) = null -- sort field
-----------------------------------------------------
, @ PageSize int = 10 -- page length
, @ Page int = 1 -- Page
-----------------------------------------------------
, @ ErrorProcedure nvarchar (128) = null output -- error stored procedure name
, @ ErrorNumber int = 0 output -- error number
, @ ErrorMessage nvarchar (512) = null output -- error message
)
As
Begin
-- Define the first record
Declare @ firstRow int;
-- Define the last record
Declare @ lastRow int; -- set the first record
Set @ firstRow = (@ Page-1) * @ PageSize) + 1;
-- Set the last record
Set @ lastRow = @ firstRow + @ PageSize; -- define the query SQL field
Declare @ sqlString nvarchar (512 );
-- Defines the Select statement, which is used for internal queries
Declare @ selectString nvarchar (512 );
-- Define the Where field for internal query
Declare @ whereString nvarchar (512 );
-- Define the Form clause and use it for internal queries
Declare @ formString nvarchar (512 );
-- Defines the grouping field, used for internal query
Declare @ groupString nvarchar (512 );
-- Defines the Having clause, which is used for internal queries
Declare @ havingString nvarchar (512 );
-- Defines the sorting field, which is used for external queries
Declare @ OrderString nvarchar (512); -- assemble the Select clause -----------------------------------
Set @ SelectField = isnull (@ SelectField, n '*');
If (@ SelectField = '')
Set @ SelectField = n '*';
-- Assemble the select clause to row_number ()
Set @ selectString = N 'select' + @ SelectField + ', Row_Number () over (';
-- Assemble the partition by expression
Set @ OverPartitionField = isnull (@ OverPartitionField, N '');
If (@ OverPartitionField <> '')
Set @ selectString = @ selectString + 'Partition by '+ @ OverPartitionField;
-- Assemble the order by expression into the sorting page
Set @ OverOrderField = isnull (@ OverOrderField, N '');
If (@ OverOrderField <> '')
Set @ selectString = @ selectString + 'Order by' + @ OverOrderField + N') as RowNumber ';
-- Over ---------------------------------------------
-- Assemble Form clause ------------------------------------
Set @ formString = n' from' + @ FormTables;
-- Assemble the Where judgment clause ------------------------------
Set @ WhereField = isnull (@ WhereField, N '');
If (@ WhereField <> '')
Set @ whereString = N 'where' + @ WhereField;
Else
Set @ whereString = N '';
-- Where judgment clause assembled -------------------------
-- Assemble group by group clause --------------------------
Set @ GroupField = isnull (@ GroupField, N '');
If (@ GroupField <> '')
Begin
Set @ groupString = n'group by' + @ GroupField; -- assemble the having group judgment condition.
Set @ HavingField = isnull (@ HavingField, N '');
If (@ HavingField <> N '')
Set @ havingString = n'having '+ @ HavingField;
Else
Set @ havingString = N '';
End
Else
Begin
Set @ groupString = N '';
Set @ havingString = N '';
End
-- Over -------------------------------------------
-- Assemble the order by external sort clause -----------------------
Set @ OrderField = isnull (@ OrderField, N '');
If (@ OrderField <> '')
Set @ OrderString = N 'Order by' + @ OverOrderField + N', '+ @ OrderField;
Else
Set @ OrderString = N '';
-- If @ groupString is not empty, external sorting does not work.
If (@ groupString <> N '')
Set @ OrderString = N '';
-- Over -------------------------------------------
-- Assemble paging query statements
Set @ sqlString =
N 'select * '+
N 'from' +
N' ('+
@ SelectString +
@ FormString +
@ WhereString +
@ GroupString +
@ HavingString +
N ') as TB' +
N 'where' +
N 'TB. RowNumber> = '+ cast (@ firstRow as nvarchar (10) +
N' and TB. RowNumber <'+ cast (@ lastRow as nvarchar (10) +
@ OrderString
-- Over -------------------------------------------
-- Execute the SQL statement and return the number of affected rows. If an exception exists, throw the statement and assign a value to the output parameter.
Begin try
Exec (@ sqlString );
Return @ rowcount;
End try
Begin catch
Set @ ErrorProcedure = Error_Procedure ();
Set @ ErrorNumber = error_number ();
Set @ ErrorMessage = error_message ();
End catch; end
Go -----------------------------------------------------------
-- Perform a simple test to query the Person. Address table of the AdventureWorks Library.
-- Paginated by addressid and sorted by PostalCode, StateProvinceID, and other fields. Exec dbo. syspr_TablePaginationSearch
@ SelectField = '*'
, @ FormTables = 'adventureworks. Person. Address'
, @ WhereField = 'ssid SSID> 100'
, @ OverOrderField = 'ssid SSID asc'
, @ OrderField = 'postalcode, stateprovinceid'
, @ Page = 1
, @ PageSize = 10
Go
-- In a simple test, the addressid field is grouped and the result set is displayed on pages. Exec dbo. syspr_TablePaginationSearch
@ SelectField = 'count (addressid) as addressCount, City'
, @ FormTables = 'adventureworks. Person. Address'
, @ OverOrderField = 'count (addressid) desc'
, @ GroupField = 'city'
, @ Page = 1
, @ PageSize = 10
Go
-- Connection query and paging
Exec dbo. syspr_TablePaginationSearch
@ SelectField = N'
Addr. AddressLine1,
Addr. AddressLine2,
Addr. City,
Sp. Name as ProvinceName,
Addr. PostalCode,
Addr. rowguid,
Addr. ModifiedDate'
, @ FormTables = N'
AdventureWorks. Person. Address as addr
Inner join
AdventureWorks. Person. StateProvince as sp
On addr. StateProvinceID = sp. StateProvinceID'
, @ OverOrderField = 'ssid SSID asc'
, @ Page = 1
, @ PageSize = 10
Go |
