/***** Object: StoredProcedure [dbo]. [P_viewPage] script date: 05/14/2012 08:49:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROC [dbo]. [P_viewPage]
-- Add the parameters for the stored procedure here
@ 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 (8000), -- 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 added to prevent extra result sets from
-- Interfering with SELECT statements.
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 parameter error ')
RETURN
END
IF @ SortType = 3
BEGIN
IF (UPPER (RIGHT (@ Order, 4 ))! = 'Asc 'and upper (RIGHT (@ Order, 5 ))! = 'Desc ')
BEGIN
PRINT ('err _ 02 sorting error') RETURN END
END
DECLARE @ new_where1 VARCHAR (8000)
DECLARE @ new_where2 VARCHAR (8000)
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)
-- Output Content
SELECT @ TotalCount as n' @ totalcount ',
@ TotalPageCount as n' @ TotalPageCount' |
