A mature multi-field sorting paging storage process

A mature multi-field sorting paging stored procedure (with bugs) Favorites

Colleague's mature multi-field sorting paging Stored Procedure

SQL call:

Exec cms_searchgetdatabypage 'tblschool ', 'Id, isemp, isnom', 'isemp, isnom', 'isemp DESC, isnom DESC', 'id', 10, 1 ,''

C:

/// <Summary>

/// Retrieve data list by page 2

/// </Summary>

/// <Param name = "tablename"> table name </param>

/// <Param name = "fldnames"> select a Field List separated by commas (,). </param>

/// <Param name = "selectorderfldname"> List of sorting fields, separated by commas (,) (fields specified by keyfldname cannot be included and can be empty) </param>

/// <Param name = "orderflddesc"> sort fields and directions, such as adddate DESC and id desc (the sorting field must be indicated by selectorderfldname

 

(Optional) </param>

/// <Param name = "keyfldname"> primary key field (if there is no primary key, please specify fields that are not repeated) </param>

/// <Param name = "pagesize"> Number of records per page </param>

/// <Param name = "pageindex"> current page </param>

/// <Param name = "strwhere"> SQL condition </param>

/// <Returns> data list </returns>

Public static dataset getlist (string tablename, string fldnames, string selectorderfldname, string

 

Orderflddesc, string keyfldname, int pagesize, int pageindex, string strwhere)

{

Sqlparameter [] parameters = {

New sqlparameter ("@ tblname", sqldbtype. varchar, 255 ),

New sqlparameter ("@ fldnames", sqldbtype. varchar, 1000 ),

New sqlparameter ("@ selectorderfldname", sqldbtype. varchar, 500 ),

New sqlparameter ("@ orderflddesc", sqldbtype. varchar, 500 ),

New sqlparameter ("@ keyfldname", sqldbtype. varchar, 255 ),

New sqlparameter ("@ pagesize", sqldbtype. INT ),

New sqlparameter ("@ pageindex", sqldbtype. INT ),

New sqlparameter ("@ strwhere", sqldbtype. varchar, 1000)

};

Parameters [0]. value = tablename;

Parameters [1]. value = fldnames;

Parameters [2]. value = selectorderfldname;

Parameters [3]. value = orderflddesc;

Parameters [4]. value = keyfldname;

Parameters [5]. value = pagesize;

Parameters [6]. value = pageindex;

Parameters [7]. value = strwhere;

Return dbhelpersql. runprocedure ("cms_searchgetdatabypage", parameters, "ds ");

}

 

Stored Procedure itself:

 

Create procedure cms_searchgetdatabypage

@ Tblname varchar (255), -- table name

@ Fldnames varchar (1000), -- the list of selected fields is separated by commas (,).

@ Selectorderfldname varchar (500), -- Sort fields are separated by commas (,) (fields specified by keyfldname cannot be included and can be blank)

@ Orderflddesc varchar (500), -- sorting field and sorting direction, such as adddate DESC and id desc (the sorting field must be specified by selectorderfldname and can be empty)

@ Keyfldname varchar (255), -- primary key field

@ Pagesize Int = 10, -- page size

@ Pageindex Int = 1, -- page number

@ Strwhere varchar (1000) = ''-- Query condition (Note: Do not add where)

As

Declare @ strwherea varchar (1200) -- temporary variable, add where to sqlwhere

Declare @ strordera varchar (2000) -- first sorting type

Declare @ strorderb varchar (2000) -- Second sorting type

Declare @ strsqla varchar (4000) -- selected for the first time

Declare @ strsqlb varchar (8000) -- selected for the second time

Declare @ strsql varchar (8000) -- Final selection

/* Condition */

If @ strwhere! =''

Set @ strwherea = 'where' + @ strwhere

Else

Set @ strwherea =''

/* Select the field list */

If @ fldnames is null or rtrim (@ fldnames) =''

Set @ fldnames = '*'

/* List of sorting fields */

If not (@ selectorderfldname is null or rtrim (@ selectorderfldname) = '')

If rtrim (@ selectorderfldname) = 'id'

Set @ selectorderfldname =''

Else

Set @ selectorderfldname = ',' + @ selectorderfldname

/* Construct an order and sort by the specified Method */

If @ orderflddesc is null or rtrim (@ orderflddesc) =''

Set @ orderflddesc = 'order by id desc'

Else

Set @ orderflddesc = 'ORDER BY' + @ orderflddesc

Set @ strordera = upper (@ orderflddesc)

Set @ strorderb = Replace (@ strordera, 'desc', 'desc1 ')

Set @ strorderb = Replace (@ strorderb, 'asc ', 'desc ')

Set @ strorderb = Replace (@ strorderb, 'sc1', 'asc ')

 

/* Page 1 */

If @ pageindex = 1

Set @ strsql = 'select top '+ STR (@ pagesize) + ''+ @ fldnames +' from ['+ @ tblname +'] With (nolock) '+ @ strwherea + ''+ @ strordera

Else

Begin

-- Retrieve the total number of records

Declare @ SQL nvarchar (500)

Declare @ maxcount int

Declare @ maxpage int

Declare @ temprowcount int

Set @ SQL = 'select @ maxcount = count ('+ @ keyfldname +') from ['+ @ tblname +'] '+ @ strwherea

Exec sp_executesql @ SQL, n' @ maxcount int output', @ maxcount output

Set @ maxpage = @ maxcount/@ pagesize

If (@ maxcount % @ pagesize> 0)

Set @ maxpage = @ maxpage + 1

/* Last page */

If @ pageindex >=@ maxpage

Begin

Set @ pageindex = @ maxpage

Set @ strsqla = char (13) + '(select top' + STR (@ maxcount % @ pagesize) + ''+ @ keyfldname + @ selectorderfldname + 'from [' + @ tblname + '] As a with (nolock)' + @ strwherea + @ strorderb + ') '+ char (13)

Set @ strsqlb = char (13) + '(select' + @ keyfldname + 'from' + @ strsqla + 'as B) '+ char (13)

Set @ strsql = 'select' + @ fldnames + 'from [' + @ tblname + '] Where ([' + @ keyfldname + '] In' + @ strsqlb + ') '+ @ strordera + char (13)

End

Else

Begin

/* Not the first page or the last page */

If (@ pageindex <= @ maxpage/2)

Begin

-- First Half of the pages

Set @ temprowcount = @ pageindex * @ pagesize

/* Construct an SQL statement. This paging algorithm aims to achieve efficient sorting of non-primary keys. By Tony */

/* 1. Select perpage * pagenum records in descending order based on the specified field + primary key field */

Set @ strsqla = char (13) + '(select top' + STR (@ temprowcount) + ''+ @ keyfldname + @ selectorderfldname + 'from [' + @ tblname + '] As a with (nolock)' + @ strwherea + @ strordera + ') '+ char (13)

/* 2. Select perpage records from the selected records in ascending order */

Set @ strsqlb = char (13) + '(select top' + STR (@ pagesize) + ''+ @ keyfldname + 'from' + @ strsqla + 'as B '+ @ strorderb +') '+ char (13)

/* 3. Select the records of the primary key in the record selected for the second time from the database, sort them in descending order, and complete the query by page */

Set @ strsql = 'select' + @ fldnames + 'from [' + @ tblname + '] Where ([' + @ keyfldname + '] In' + @ strsqlb + ') '+ @ strordera

End

Else

Begin

-- The last half of the pages

Set @ temprowcount = @ maxcount-(@ pageindex-1) * @ pagesize

/* Construct an SQL statement. This paging algorithm aims to achieve efficient sorting of non-primary keys. By Tony */

/* 1. Select perpage * pagenum records in descending order based on the specified field + primary key field */

Set @ strsqla = char (13) + '(select top' + STR (@ temprowcount) + ''+ @ keyfldname + @ selectorderfldname + 'from [' + @ tblname + '] As a with (nolock)' + @ strwherea + @ strorderb + ') '+ char (13)

/* 2. Select perpage records from the selected records in ascending order */

Set @ strsqlb = char (13) + '(select top' + STR (@ pagesize) + ''+ @ keyfldname + 'from' + @ strsqla + 'as B '+ @ strordera +') '+ char (13)

/* 3. Select the records of the primary key in the record selected for the second time from the database, sort them in descending order, and complete the query by page */

Set @ strsql = 'select' + @ fldnames + 'from [' + @ tblname + '] Where ([' + @ keyfldname + '] In' + @ strsqlb + ') '+ @ strordera

End

End

End

 

Set nocount on

 

/* Print @ strsql */-- display SQL

Exec (@ strsql)

Set nocount off

Return