SQL Server stored procedures for paged queries

Source: Internet
Author: User

CREATE PROCEDURE Prcpageresult--Get data from a page--
@currPage INT = 1,--The current page number (that is, top currpage)
@showColumn VARCHAR (2000) = ' * ',--the field to be obtained (i.e. Column1,column2,......)
@tabName VARCHAR (2000),--The name of the table to be viewed (i.e. from table_name)
@strCondition VARCHAR (2000) = ",--the query condition (i.e. where condition ...) without the add where keyword
@ascColumn VARCHAR (100) = ',--Sort the field name (i.e. order by column Asc/desc)
@bitOrderType BIT = 0,--type of sort (0 is ascending, 1 is descending)
@pkColumn VARCHAR (50) = ",--primary Key name
@pageSize INT = 20-Paging size
As
Begin--The stored procedure begins--a few variables that the stored procedure needs to use--
DECLARE @strTemp VARCHAR (1000)
DECLARE @strSql VARCHAR (4000)-The last statement executed by the stored procedure
DECLARE @strOrderType VARCHAR (1000)--Sort Type statement (order BY column ASC or ORDER BY column DESC)
BEGIN
IF @bitOrderType = 1--bitordertype=1 to perform descending
BEGIN
SET @strOrderType = ' ORDER by ' + @ascColumn + ' DESC '
SET @strTemp = ' < (SELECT min ')
END
ELSE
BEGIN
SET @strOrderType = ' ORDER by ' + @ascColumn + ' ASC '
SET @strTemp = ' > (SELECT max ')
END
If @currPage = 1--If it is the first page
BEGIN
IF @strCondition! = "
SET @strSql = ' SELECT TOP ' + STR (@pageSize) + '
+ @showColumn + ' from ' + @tabName + ' WHERE '
+ @strCondition + @strOrderType
ELSE
SET @strSql = ' SELECT TOP ' + STR (@pageSize) + '
+ @showColumn + ' from ' + @tabName
+ @strOrderType

END
else--Other pages
BEGIN

IF @strCondition! = "
SET @strSql = ' SELECT TOP ' + STR (@pageSize) + '
+ @showColumn + ' from ' + @tabName + ' WHERE '
+ @strCondition + ' + ' + @pkColumn + @strTemp
+ ' (' + @pkColumn + ') ' + ' from (SELECT TOP ')
+ STR ((@currPage-1) * @pageSize) + '
+ @pkColumn + ' from ' + @tabName + @strOrderType
+ ') as Tabtemp) ' + @strOrderType
ELSE
SET @strSql = ' SELECT TOP ' + STR (@pageSize) + '
+ @showColumn + ' from ' + @tabName + ' WHERE '
+ @pkColumn + @strTemp + ' (' + @pkColumn + ') '
+ ' from (SELECT TOP ' + STR (@currPage-1)
* @pageSize) + '
+ @pkColumn + ' from ' + @tabName + @strOrderType
+ ') as Tabtemp) ' + @strOrderType
END
END
EXEC (@strSql)
END


EXEC Prcpageresult 2, ' * ', ' outpr_register ', ' state=1 ', ' inputtime ', 1, ' Registerid ', 3

SQL Server stored procedures for paged queries

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.