Http://www.594jsh.cn/Look.asp? Id = 75
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 characters, such as id asc and 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 (replace (@ new_order2, ASC, {ASC},), DESC, {DESC },)
Set @ new_order2 = Replace (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 (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
