Page 1/2 of SQL Server Stored Procedure Code

Source: Internet
Author: User

Copy codeThe Code is as follows:
Declare @ TotalCount int
Declare @ TotalPageCount int
Exec P_viewPage_A 'type1', '*', 'id', ', 'Id asc', @ TotalCount output, @ TotalPageCount output

Select * from type1


Create PROC P_viewPage_A
/*
Nzperfect [no_mIss] efficient and common paging Stored Procedure (bidirectional retrieval) 2007.5.7 QQ: 34813284
Tip: Applicable to tables or views with a single primary key or a unique value column
Ps: the SQL statement is 8000 bytes. Note that the input parameters and total SQL length must not exceed the specified range.
*/
@ TableName VARCHAR (200), -- table name
@ FieldList VARCHAR (2000), -- displays the column name. If it is all fields, it is *
@ PrimaryKey VARCHAR (100), -- single primary key or unique value Key
@ Where VARCHAR (2000), -- the query condition does not contain the 'where' character, for example, id> 10 and len (userid)> 9
@ Order VARCHAR (1000), -- sorting does not contain the 'ORDER BY' character, such as id asc, userid desc. asc or desc must be specified.
-- Note that it takes effect when @ SortType = 3. Remember to add the primary key at the end; otherwise, it will make you depressed.
@ SortType INT, -- sorting Rule 1: Forward asc 2: reverse desc 3: Multi-column sorting method
@ RecorderCount INT, -- total number of records 0: the total number of records is returned.
@ PageSize INT, -- number of records output per page
@ PageIndex INT, -- current page number
@ TotalCount int output, -- Record total returned records
@ TotalPageCount int output -- total number of returned pages
AS
SET NOCOUNT ON
If isnull (@ TotalCount, '') ='' SET @ TotalCount = 0
SET @ Order = RTRIM (LTRIM (@ Order ))
SET @ PrimaryKey = RTRIM (LTRIM (@ PrimaryKey ))
SET @ FieldList = REPLACE (RTRIM (LTRIM (@ FieldList )),'','')
While charindex (',', @ Order)> 0 or charindex (',', @ Order)> 0
BEGIN
SET @ Order = REPLACE (@ Order ,',',',')
SET @ Order = REPLACE (@ Order ,',',',')
END
If isnull (@ TableName, '') ='' or isnull (@ FieldList, '') =''
Or isnull (@ PrimaryKey, '') =''
OR @ SortType <1 OR @ SortType> 3
OR @ RecorderCount <0 OR @ PageSize <0 OR @ PageIndex <0
BEGIN
PRINT ('err _ 00 ')
RETURN
END
IF @ SortType = 3
BEGIN
IF (UPPER (RIGHT (@ Order, 4 ))! = 'Asc 'and upper (RIGHT (@ Order, 5 ))! = 'Desc ')
Begin print ('err _ 02 ') RETURN END
END
DECLARE @ new_where1 VARCHAR (1000)
DECLARE @ new_where2 VARCHAR (1000)
DECLARE @ new_order1 VARCHAR (1000)
DECLARE @ new_order2 VARCHAR (1000)
DECLARE @ new_order3 VARCHAR (1000)
DECLARE @ SQL VARCHAR (8000)
DECLARE @ SqlCount NVARCHAR (4000)
If isnull (@ where, '') =''
BEGIN
SET @ new_where1 =''
SET @ new_where2 = 'where'
END
ELSE
BEGIN
SET @ new_where1 = 'where' + @ WHERE
SET @ new_where2 = 'where' + @ WHERE + 'and'
END
If isnull (@ order, '') ='' OR @ SortType = 1 OR @ SortType = 2
BEGIN
IF @ SortType = 1
BEGIN
SET @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'asc'
SET @ new_order2 = 'ORDER BY' + @ PrimaryKey + 'desc'
END
IF @ SortType = 2
BEGIN
SET @ new_order1 = 'ORDER BY' + @ PrimaryKey + 'desc'
SET @ new_order2 = 'ORDER BY' + @ PrimaryKey + 'asc'
END
END
ELSE
BEGIN
SET @ new_order1 = 'ORDER BY' + @ ORDER
END
IF @ SortType = 3 and charindex (',' + @ PrimaryKey + '', ',' + @ Order)> 0
BEGIN
SET @ new_order1 = 'ORDER BY' + @ ORDER
SET @ new_order2 = @ Order + ','
SET @ new_order2 = REPLACE (@ new_order2, 'asc, ',' {ASC}, '), 'desc',' {DESC },')
SET @ new_order2 = REPLACE (@ new_order2, '{ASC},', 'desc'), '{DESC},', 'asc ,')
SET @ new_order2 = 'ORDER BY' + SUBSTRING (@ new_order2, 1, LEN (@ new_order2)-1)
IF @ FieldList <> '*'
BEGIN
SET @ new_order3 = REPLACE (@ Order + ',', 'asc, ','), 'desc ,',',')
SET @ FieldList = ',' + @ FieldList
While charindex (',', @ new_order3)> 0
BEGIN
If charindex (SUBSTRING (',' + @ new_order3, 1, CHARINDEX (',', @ new_order3), ',' + @ FieldList + ',')> 0
BEGIN
SET @ FieldList =
@ FieldList + ',' + SUBSTRING (@ new_order3, 1, CHARINDEX (',', @ new_order3 ))
END
SET @ new_order3 =
SUBSTRING (@ new_order3, CHARINDEX (',', @ new_order3) + 1, LEN (@ new_order3 ))
END
SET @ FieldList = SUBSTRING (@ FieldList, 2, LEN (@ FieldList ))
END
END
SET @ SqlCount = 'select @ TotalCount = COUNT (*), @ TotalPageCount = CEILING (COUNT (*) + 0.0 )/'
+ CAST (@ PageSize as varchar) + ') FROM' + @ TableName + @ new_where1
IF @ RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @ SqlCount, n' @ TotalCount int output, @ TotalPageCount INT output ',
@ TotalCount OUTPUT, @ TotalPageCount OUTPUT
END
ELSE
BEGIN
SELECT @ TotalCount = @ RecorderCount
END
IF @ PageIndex> CEILING (@ TotalCount + 0.0)/@ PageSize)
BEGIN
SET @ PageIndex = CEILING (@ TotalCount + 0.0)/@ PageSize)
END
IF @ PageIndex = 1 OR @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize)
BEGIN
IF @ PageIndex = 1 -- returns the first page of data
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from'
+ @ TableName + @ new_where1 + @ new_order1
END
IF @ PageIndex> = CEILING (@ TotalCount + 0.0)/@ PageSize) -- returns the last page of data
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (ABS (@ PageSize * @ PageIndex-@ TotalCount-@ PageSize ))
+ ''+ @ FieldList + 'from'
+ @ TableName + @ new_where1 + @ new_order2 + ') as tmp'
+ @ New_order1
END
END
ELSE
BEGIN
IF @ SortType = 1 -- Only sort the primary key in positive order
BEGIN
IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '>'
+ '(Select max (' + @ PrimaryKey + ') FROM (select top'
+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order1 + ') as tmp)' + @ new_order1
END
ELSE -- reverse Retrieval
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select TOP '+ STR (@ PageSize) +''
+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '<'
+ '(Select min (' + @ PrimaryKey + ') FROM (select top'
+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order2 + ') as tmp)' + @ new_order2
+ ') As tmp' + @ new_order1
END
END
IF @ SortType = 2 -- Only sort the primary key in reverse order
BEGIN
IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '<'
+ '(Select min (' + @ PrimaryKey + ') FROM (select top'
+ STR (@ PageSize * (@ PageIndex-1) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order1 + ') as tmp)' + @ new_order1
END
ELSE -- reverse Retrieval
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select TOP '+ STR (@ PageSize) +''
+ @ FieldList + 'from'
+ @ TableName + @ new_where2 + @ PrimaryKey + '>'
+ '(Select max (' + @ PrimaryKey + ') FROM (select top'
+ STR (@ TotalCount-@ PageSize * @ PageIndex) + ''+ @ PrimaryKey
+ 'From' + @ TableName
+ @ New_where1 + @ new_order2 + ') as tmp)' + @ new_order2
+ ') As tmp' + @ new_order1
END
END
IF @ SortType = 3 -- Multi-column sorting, which must contain the primary key and be placed at the end; otherwise, it is not processed.
BEGIN
If charindex (',' + @ PrimaryKey + ',', '+ @ Order) = 0
Begin print ('err _ 02 ') RETURN END
IF @ PageIndex <= CEILING (@ TotalCount + 0.0)/@ PageSize)/2 -- forward search
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ PageSize * @ PageIndex) + ''+ @ FieldList
+ 'From' + @ TableName + @ new_where1 + @ new_order1 + ') as tmp'
+ @ New_order2 + ') as tmp' + @ new_order1
END
ELSE -- reverse Retrieval
BEGIN
SET @ SQL = 'select TOP '+ STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ PageSize) + ''+ @ FieldList + 'from ('
+ 'Select top' + STR (@ TotalCount-@ PageSize * @ PageIndex + @ PageSize) + ''+ @ FieldList
+ 'From' + @ TableName + @ new_where1 + @ new_order2 + ') as tmp'
+ @ New_order1 + ') as tmp' + @ new_order1
END
END
END
EXEC (@ SQL)
GO

How to Use vc # Call the above Stored Procedure

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.