Another common paging Stored Procedure supports table alias and multi-table joint query of SQL statements

I haven't been writing anything for a long time. Today I am free to share some of my personal experiences, that is, the paging storage process. This issue should be a repeat by the old man, there are already enough types of general stored procedures on the Internet, but it seems that the pages of some complex SQL statements are basically not enough (or I may not be knowledgeable enough ), for example

select '' as CheckBox, A.TargetID, A.TargetPeriod, Convert(varchar(10),

B.BeginDate, 120) as BeginDate,

Convert(varchar(10), B.EndDate, 120) as EndDate, C.SalesCode,

C.SalesName, D.CatalogCode, D.CatalogName,

E.OrgID, E.OrgName, F.OrgID as BranchOrgID, F.OrgCode as

BranchOrgCode, F.OrgName as BranchOrgName,

A.Amount, '' as DetailButton

from ChlSalesTarget as A

left outer join ChlSalesTargetPeriod as B on A.TargetPeriod=B.TargetPeriod

left outer join ChlSales as C on A.Sales=C.SalesCode

left outer join ChlItemCatalog as D on A.ItemCatalog=D.CatalogCode

left outer join ChlOrg as E on A.OrgID=E.OrgID

left outer join ChlOrg as F on C.BranchOrgID=F.OrgID

where A.TargetPeriod >='200607' and A.TargetPeriod <='200608' and F.OrgCode

like '%123%' and E.OrgCode like '%123%'

order by A.TargetPeriod desc,C.SalesName,D.CatalogName

The preceding SQL statement contains some special cases, such as the Convert function, no primary key, multi-table join, table alias, and field alias. These cases may be tricky to handle, of course, the "'' as CheckBox "is a special case in my system and is used for some processing.

I am here to provide a self-developed general paging storage process. If you have any suggestions or comments, please kindly advise. The Code is as follows:

General paging stored procedure-Sp_Paging

/**//*

Function: General paging Stored Procedure

Parameters:

@ PK varchar (50 ),

Primary Key, a single field used for sorting. If it is null, it indicates that there is no primary key. The primary key of the ID column is automatically created during the stored procedure.

@ Fields varchar (500): List of Fields to be displayed (Format: ID, Code, Name)

@ Tables varchar (1000): The table set to be used (Org)

@ Where varchar (500), query condition (Code like '20140901 ')

@ Orderby varchar (100), sorting condition (supports multiple sorting fields, such as ID, code DESC, name DESC)

@ Pageindex INT: The index of the page to be displayed. The index starts from 1 and is 0 if no record exists.

@ Pagesize int, page size

Created by: holis Yao

Created on: 2006-08-06

Note:

*/

Create procedure [DBO]. [sp_paging]

@ PK varchar (50) = '',

@ Fields varchar (500 ),

@ Tables varchar (1000 ),

@ Where varchar (500) = '',

@ Orderby varchar (100 ),

@ Pageindex int,

@ Pagesize int

As

-- Replace single quotes to avoid SQL construction errors

Set @ Fields = replace (@ Fields ,'''','''''')

-- The SQL statement to be executed is split into several strings to avoid the problem that the length exceeds 4 K.

Declare @ SQL1 varchar (4000)

Declare @ SQL2 varchar (4000)

Set @ SQL1 =''

Set @ SQL2 =''

If @ Where is not null and len (ltrim (rtrim (@ Where)> 0

Set @ Where = 'where' + @ where

Else

Set @ Where = 'where 1 = 1'

Set @ SQL1 = @ SQL1 + 'Clare @ TotalCount int' -- declare a variable, total number of records

Set @ SQL1 = @ SQL1 + 'Clare @ PageCount int' -- declares a variable, total number of pages

Set @ SQL1 = @ SQL1 + 'Clare @ PageIndex int' -- declare a variable, page index

Set @ SQL1 = @ SQL1 + 'Clare @ StartRow int' -- declares a variable, the index of the first record on the current page

Set @ sql1 = @ sql1 + 'select @ totalcount = count (*) from' + @ tables + @ where -- get the total number of records

Set @ sql1 = @ sql1 + 'if @ pagecount <= 0 in '-- if the number of records is 0, an empty result set is output directly.

Set @ sql1 = @ sql1 + 'select' + @ fields + 'from' + @ tables + 'where 1 <> 1'

Set @ sql1 = @ sql1 + 'select 0 as pageindex, 0 as pagecount ,'

+ Convert (varchar, @ pagesize) + 'as pagesize, 0 as totalcount'

Set @ sql1 = @ sql1 + 'Return end'

Set @ sql1 = @ sql1 + 'set @ pagecount = (@ totalcount + '+ convert (varchar, @ pagesize)

+ '-1)/' + convert (varchar, @ pagesize) -- get the total number of pages

Set @ sql1 = @ sql1 + 'set @ pageindex = '+ convert (varchar, @ pageindex)

-- Set the correct page index

Set @ sql1 = @ sql1 + 'if @ pageindex <0 set @ pageindex = 1'

Set @ SQL1 = @ SQL1 + 'if @ PageIndex> @ PageCount and @ PageCount> 0

Set @ PageIndex = @ PageCount'

Set @ SQL1 = @ SQL1 + 'set @ StartRow = (@ PageIndex-1) * '+ convert (varchar, @ PageSize)

+ '+ 1'

If (charindex (',', @ OrderBy) = 0 and charindex (@ PK, @ OrderBy)> 0)

Begin

--*************************************** *************************************

-- **************** You do not need to create a primary key ******************* *************************

--*************************************** *************************************

Declare @ SortDirection varchar (10) -- sort direction, >=: ascending, <=: reverse

Set @ SortDirection = '> ='

If charindex ('desc', @ OrderBy)> 0

Set @ SortDirection = '<='

Set @ SQL2 = @ SQL2 + 'Clare @ Sort varchar (100 )'

-- Declare a variable to record the sorting field value of the first record on the current page

Set @ SQL2 = @ SQL2 + 'set rowcount @ startrow'

-- Set the number of returned records to the first one on the current page.

Set @ SQL2 = @ SQL2 + 'select @ Sort = '+ @ PK + 'from'

+ @ Tables + @ Where + 'ORDER BY' + @ OrderBy -- obtains the value of the first sorting field on the current page.

Set @ SQL2 = @ SQL2 + 'set rowcount' + convert (varchar, @ PageSize)

-- Set the number of returned records to the page size

Set @ Where = @ Where + 'and' + @ PK + @ SortDirection + '@ Sort'

Set @ SQL2 = @ SQL2 + 'select' + @ Fields + 'from' + @ Tables

+ @ Where + 'ORDER BY' + @ OrderBy

-- Output the final display result

End

Else

Begin

You need to create an auto-increment primary key.

Set @ SQL2 = @ SQL2 + 'Clare @ EndRow int'

Set @ SQL2 = @ SQL2 + 'set @ EndRow = @ PageIndex * '+ convert (varchar, @ PageSize)

Set @ SQL2 = @ SQL2 + 'set rowcount @ endrow'

Set @ SQL2 = @ SQL2 + 'Clare @ PKBegin int' -- declare a variable and start the index.

Set @ SQL2 = @ SQL2 + 'Clare @ PKEnd int' -- declare a variable and end the index.

Set @ SQL2 = @ SQL2 + 'set @ PKBegin = @ startrow'

Set @ SQL2 = @ SQL2 + 'set @ PKEnd = @ endrow'

--*************************************** *************************************

-- ************* Convert special fields, this allows you to insert data to a temporary table ******************

--*************************************** *************************************

Declare @ TempFields varchar (500)

Set @ TempFields = @ Fields

Set @ TempFields = replace (@ TempFields, ''' as checkbox ','')

Set @ TempFields = replace (@ TempFields, ''' as DetailButton ','')

Set @ TempFields = replace (@ TempFields, ''' as Radio ','')

Set @ TempFields = LTRIM (RTRIM (@ TempFields ))

If left (@ TempFields, 1) = ',' -- remove the leftmost comma

Set @ TempFields = substring (@ TempFields, 2, len (@ TempFields ))

If right (@ TempFields, 1) = ',' -- remove the rightmost comma

Set @ TempFields = substring (@ TempFields, 1, len (@ TempFields)-1)

Set @ SQL2 = @ SQL2 + 'select identity (int, 1, 1) as PK, '+ @ TempFields

+ 'Into # tb from' + @ Tables + @ Where + 'ORDER BY' + @ OrderBy

--*************************************** *************************************

-- ******** The table name prefix of the field is removed. When a field has an alias, only the field alias is retained *********

--*************************************** *************************************

Declare @ TotalFields varchar (500)

Declare @ tmp varchar (50)

Declare @ I int

Declare @ j int

Declare @ iLeft int -- number of left parentheses

Declare @ iRight int -- number of parentheses

Set @ I = 0

Set @ J = 0

Set @ ileft = 0

Set @ iright = 0

Set @ TMP =''

Set @ totalfields =''

While (LEN (@ fields)> 0)

Begin

Set @ I = charindex (',', @ fields)

-- Remove the table name prefix of a field. This article is published on www.xker.com)

If (@ I = 0)

Begin

-- The comma-separated field is not found, indicating that only the last field is left.

Set @ TMP = @ Fields

End

Else

Begin

Set @ TMP = substring (@ fields, 1, @ I)

End

Set @ J = charindex ('.', @ TMP)

If (@ j> 0)

Set @ TMP = substring (@ TMP, @ J + 1, Len (@ TMP ))

-- ******* When a field has an alias, only the field alias is reserved *********

-- The case with parentheses should be handled separately, such as convert (varchar (10), B. enddate, 120) as enddate

While (charindex (', @ TMP)> 0)

Begin

Set @ ileft = @ ileft + 1

Set @ TMP = substring (@ TMP, charindex (', @ TMP) + 1, Len (@ TMP ))

End

While (charindex (')', @ TMP)> 0)

Begin

Set @ iright = @ iright + 1

Set @ TMP = substring (@ TMP, charindex (')', @ TMP) + 1, Len (@ TMP ))

End

-- The Field alias can be processed only when the brackets match the team.

If (@ ileft = @ iright)

Begin

Set @ ileft = 0

Set @ iright = 0

-- Do not process these special fields: checkbox, detailbutton, and radio

If (charindex ('checkbox', @ TMP) = 0 and charindex

('Detailbutton', @ TMP) = 0 and charindex ('radio', @ TMP) = 0)

Begin

-- Determine whether an alias exists

If (charindex ('as', @ TMP)> 0) -- the first method of Alias, in the format of 'as'

Begin

Set @ TMP = substring (@ TMP, charindex ('as', @ TMP) + 2, Len (@ TMP ))

End

Else

Begin

If (charindex ('', @ tmp)> 0) -- the second method of Alias, with space (" ") Format

Begin

While (charindex ('', @ tmp)> 0)

Begin

Set @ tmp = substring (@ tmp, charindex ('', @ tmp) + 1, len (@ tmp ))

End

End

End

End

Set @ TotalFields = @ TotalFields + @ tmp

End

If (@ I = 0)

Set @ Fields =''

Else

Set @ Fields = substring (@ Fields, @ I + 1, len (@ Fields ))

End

-- Print @ TotalFields

Set @ SQL2 = @ SQL2 + 'select' + @ TotalFields +'

From # tb where PK between @ PKBegin and @ PKEnd order by pK'

-- Output the final display result

Set @ SQL2 = @ SQL2 + 'drop table # tb'

End

-- Output "PageIndex (page index), PageCount (page number), PageSize (page size), TotalCount (total number of records )"

Set @ SQL2 = @ SQL2 + 'select @ PageIndex as PageIndex, @ PageCount as PageCount ,'

+ Convert (varchar, @ PageSize) + 'as PageSize, @ TotalCount as totalcount'

-- Print @ SQL1 + @ SQL2

Exec (@ SQL1 + @ SQL2)

If you use this generic paging stored procedure, the call method is as follows:

Paging using common paging stored procedures

/**//*

Function: gets the sales target based on the conditions.

Parameters:

@ UserType int,

@ OrgID varchar (500 ),

@ TargetPeriodBegin nvarchar (50 ),

@ TargetPeriodEnd nvarchar (50 ),

@ BranchOrgCode nvarchar (50 ),

@ BranchOrgName nvarchar (50 ),

@ OrgCode nvarchar (50 ),

@ OrgName nvarchar (50 ),

@ SalesCode nvarchar (50 ),

@ SalesName nvarchar (50 ),

@ CatalogCode nvarchar (50 ),

@ CatalogName nvarchar (50 ),

@ PageIndex int: The index of the page to be displayed. The index starts from 1 and is 0 if no record exists.

@ PageSize int, page size

Created by: holis Yao

Created on: 2006-08-11

Note:

========================================================== ================================

*/

Create procedure [dbo]. [GetSalesTargetList]

@ UserType int,

@ OrgID nvarchar (500 ),

@ TargetPeriodBegin nvarchar (50 ),

@ TargetPeriodEnd nvarchar (50 ),

@ BranchOrgCode nvarchar (50 ),

@ BranchOrgName nvarchar (50 ),

@ OrgCode nvarchar (50 ),

@ OrgName nvarchar (50 ),

@ SalesCode nvarchar (50 ),

@ SalesName nvarchar (50 ),

@ CatalogCode nvarchar (50 ),

@ CatalogName nvarchar (50 ),

@ PageIndex int,

@ PageSize int

AS

Declare @ Condition nvarchar (2000)

Set @ Condition =''

If (@ UserType <> 1)

Set @ Condition = @ Condition + 'and A. OrgID in (' + @ OrgID + ')'

If (len (@ TargetPeriodBegin)> 0)

Set @ Condition = @ Condition + 'and A. TargetPeriod> = ''' + @ TargetPeriodBegin + ''''

If (len (@ TargetPeriodEnd)> 0)

Set @ Condition = @ Condition + 'and A. TargetPeriod <= ''' + @ TargetPeriodEnd + ''''

If (len (@ BranchOrgCode)> 0)

Set @ Condition = @ Condition + 'and F. OrgCode like ''' %' + @ BranchOrgCode + '% '''

If (len (@ BranchOrgName)> 0)

Set @ Condition = @ Condition + 'and F. OrgName like ''%' + @ BranchOrgName + '% '''

If (len (@ OrgCode)> 0)

Set @ Condition = @ Condition + 'and E. OrgCode like ''' %' + @ OrgCode + '% '''

If (len (@ OrgName)> 0)

Set @ Condition = @ Condition + 'and E. OrgName like ''%' + @ OrgName + '% '''

If (len (@ SalesCode)> 0)

Set @ Condition = @ Condition + 'and C. SalesCode like ''%' + @ SalesCode + '% '''

If (len (@ SalesName)> 0)

Set @ Condition = @ Condition + 'and C. SalesName like ''%' + @ SalesName + '% '''

If (len (@ CatalogCode)> 0)

Set @ Condition = @ Condition + 'and D. CatalogCode like ''' %' + @ CatalogCode + '% '''

If (len (@ CatalogName)> 0)

Set @ Condition = @ Condition + 'and D. CatalogName like ''%' + @ CatalogName + '% '''

If (len (@ Condition)> 0)

Set @ Condition = substring (@ Condition, 5, len (@ Condition ))

-- Print @ Condition

Exec sp_Paging

N '', N ''' as CheckBox, A. TargetID, A. TargetPeriod, Convert (varchar (10 ),

B. BeginDate, 120) as BeginDate, Convert (varchar (10), B. EndDate, 120) as EndDate,

C. SalesCode, C. SalesName, D. CatalogCode, D. CatalogName, E. OrgID, E. OrgName,

F. OrgID as BranchOrgID, F. OrgCode as BranchOrgCode, F. OrgName as BranchOrgName,

A. Amount, ''as DetailButton ',

N' ChlSalesTarget as

Left outer join ChlSalesTargetPeriod as B on A. TargetPeriod = B. TargetPeriod

Left outer join ChlSales as C on A. Sales = C. SalesCode

Left outer join ChlItemCatalog as D on A. ItemCatalog = D. CatalogCode

Left outer join ChlOrg as E on A. OrgID = E. OrgID

Left outer join ChlOrg as F on C. BranchOrgID = F. OrgID ',

@ Condition,

N'a. TargetPeriod desc, C. SalesName, D. catalogname ',

@ PageIndex, @ PageSize