Main topic: Have a good example of a good stored procedure paging! Author: weihua99 (I love DotNet) credit: 80 belongs forum: .NET ASP.NET technology issues Points: 50 Replies: 6 Time: 2004-10-12 11:01:45 To ensure the smooth flow of the code to run, I I have seen a lot in the forum, and the code will perform an error.
Thank you!
Reply to: Littlehb (closed door ing ...) () Reputation: 100 2004-10-12 11:04:32 Score: 0 Singapore Control:
http://www.webdiyer.com
TOP
Reply to: yuewenbin () Reputation: 95 2004-10-12 11:04:54 Score: 0 http://207.46.156.252/China/community/column/49.mspx
TOP
Reply to: Gyhongjun (Red Army) () Reputation: 100 2004-10-12 11:25:30 Score: 0 Create Procedure GetRecordfromPage
@tblname varchar (255), - Table name
@fldname varchar (255), - field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Page
@Iscount bit = 0, - Return to the total number of records, non-0 values return
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1000) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (1000) - main statement
Declare @strtmp varchar (300) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @ORDERTYPE! = 0
Begin
Set @strtmp = "<(SELECT MIN"
Set @strorder = "Order by [" @fldname "] desc"
end
Else
Begin
Set @strtmp = "> (SELECT MAX"
Set @strorder = "Order by [" @fldname "] ASC"
end
Set @strsql = "SELECT TOP" STR (@PageSize) "* from ["
@TBLNAME "] Where [" @fldname "]" @strtmp "(["
@fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "[" @fldname "] from [" @tblname "]" @strorder " ) AS TBLTMP) "
@strorder
IF @strwhere! = ''
Set @strsql = "SELECT TOP" STR (@PageSize) "* from ["
@TBLNAME "] Where [" @fldname "]" @strtmp "(["
@fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "["
@fldname "] from [" @TBLNAME "] Where (" @strwhere ")"
@strorder ") as tbltmp) and (" @strwhere ")" @strorder
IF @PageIndex = 1
Begin
Set @strtmp = ""
IF @strwhere! = ''
Set @strtmp = "Where (" @strwhere ")"
Set @strsql = "SELECT TOP" STR (@PageSize) "* from ["
@TBLNAME "]" @strtmp "" @strorder
end
IF @iscount! = 0
Set @strsql = "Select count (*) as total from [" @TBLNAME "]"
EXEC (@strsql)
Go
TOP
Reply to: jianli0108 (laser) () Reputation: 98 2004-10-12 11:32:43 Score: 0 / * Paging Storage Process
Descript: Paging Store
Author: blue.dream
Date: 2004-8-18 21:01
* /
Create Procedure ListPage
@TBLNAME NVARCHAR (200), ------ To display the table or multiple tables of tables
@fldname nvarchar (200) = '*', --- List of fields to display
@PageSize INT = 10, ---- The number of records shown per page is @Page Int = 1, ---- To display the record of the page
@PageCount int = 1 OUTPUT, ---- Query result number after page number
@Counts int = 1 Output, ------ Query number
@fldsort nvarchar (100) = NULL, ---- Sort field list or condition
@Sort bit = 0, ---- Sorting method, 0 is ascending, 1 is descending
@strcondition nvarchar (200) = NULL, ---- Query criteria, no WHERE
@ID nvarchar (50) ---- primary key of the main table
)
AS
Set nocount on
Declare @sqltmp nvarchar (1000) ---- store dynamically generated SQL statement
Declare @strtmp nvarchar (1000) ---- Store query statement for the total number of query results
Declare @strid nvarchar (1000) ---- Store Query Statement for the start or end ID of the query
Declare @sqlsort nvarchar (200) ---- Store temporary generation sorting conditions
Declare @intcounts int ---- The number of records to move
Declare @beginid Int ---- Start ID
Declare @endid Int ---- Ended ID
-------- First result in the sorting method ---------
IF @ sort = 0 - ascending
Begin
IF not (@fldsort is null)
Set @sqlsort = 'Order by' @fldsort
Else
Set @sqlsort = 'Order by' @ID
end
Else - descending order
Begin
IF not (@fldsort is null)
Set @sqlsort = 'ORDER BY' @fldsort 'Desc'
Else
Set @sqlsort = 'ORDER BY' @ID 'DESC'
end
-------- Generate query statement --------
- Here @STRTMP is a statement of the number of query results
If @strcondition is null - no display condition
Begin
Set @Sqltmp = @fldname 'from' @TBLNAME
Set @strtmp = 'select @ counts = count (' @ID ') from' @ TBLNAME
Set @strid = 'from' @tblname
end
Else
Begin
Set @Sqltmp = @fldname 'from' @TBLNAME 'Where' @StrconditionSet @strtmp = 'select @ counter = count (' @id ') from' @ TBLNAME 'Where' @strcondition
Set @strid = 'from' @tblname 'Where' @strcondition
end
---- Total quantity of the query results -----
EXEC SP_EXECUTESQL @ stratmp, n '@ counts int out', @ counts OUT
- Get the total number of paging
IF @counts <= @PageSize
Set @PageCount = 1
Else
Set @PageCount = (@counts / @PageSize) 1
- Calculate the number of records to move
IF @Page = 1
Set @intcounts = @PageSize
Else
Begin
Set @intcounts = (@ Page-1) * @PageSize 1
end
----- The ID of the first record of this page after the page
Set @strid = 'select @ beginge =' @id '' @strid
Set @intcounts = @intcounts - @Pagesize 1
SET ROWCOUNT @intcounts
EXEC SP_EXECUTESQL @ Strid, N '@ Beginid Int Out', @ Beginge Out
----- The ID of the last record of this page after obtaining paging
Set @intcounts = @intcounts @PageSize - 1
Print @intcounts
SET ROWCOUNT @intcounts
EXEC SP_EXECUTESQL @ Strid, N '@ beginge Int Out', @ endid out
------ Restore System Settings -----
SET ROWCOUNT 0
Set nocount off
------ Return to Query Results -----
IF @strcondition is null
Set @Strtmp = 'SELECT' @Sqltmp 'Where' @id 'Between' Str (@beginid) 'and' str (@endid)
Else
Set @strtmp = 'SELECT' @Sqltmp 'Where' @id '(Between' Str (@Beginid) 'and' Str (@endid) ') and' @Strconditionif Not (@sqlsort is NULL)
Set @strtmp = @Strtmp @sqlsort
EXEC SP_EXECUTESQL @STRTMP
Go
TOP
Reply to: weihua99 (I love DOTNET) () Reputation: 80 2004-10-12 11:43:14 Score: 0 It is best to have an example of calling the stored procedure.
TOP
Reply to: Serverme (too sleepy, want to sleep) () Reputation: 100 2004-10-12 16:04:00 Score: 0 This is what I wrote in ASP. Net is good.
---------------------------------------------- ---------------
- Implement paging using SQL unprecedented stored procedures
Create Procedure P_splitpage
@sql nvarchar (4000), - SQL statement to be executed
@Page INT = 1, - The page number to display
@PageSize Int, - Size per page
@PageCount INT = 0 out, - Total number of pages
@Recordcount Int = 0 OUT - total record number
AS
Set nocount on
Declare @ p1 int
EXEC SP_CURSoropen @ p1 output, @ SQL, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ PageCount Output
Set @Recordcount = @PageCount
SELECT @ PageCount = CEILING (1.0 * @ Pagecount / @ Pagesize)
, @ Page-1) * @ PageSize 1
EXEC SP_CURSORFETCH @ P1, 16, @ Page, @PageSize
EXEC SP_CURSORCLOSE @ P1
Go
------------------------------------------- -
Create Procedure DTALYJD_SEARCH (@search_name varchar (50)) AS
Declare @str varchar (4000)
IF @ search_name = ''
Begin
Set @ Str = 'SELECT * FROM table name
SELECT @str sql
Return
end
Else
Begin
Set @ Str = 'SELECT * FROM Name WHERE
Search_name like ''% ' @ Search_name '% ''
SELECT @str sql
Return
end
Go
----------------------------------------------- < %
DIM Search_name
Search_name = trim (Request ("Search_name")))
SET RS1 = conn.execute ("Exec DTALYJD_SEARCH '" & Search_name & "')
SQL = RS1 ("SQL")
Rs1.close
If Request ("Page" = "" "= 0 or isnumeric (Request (" Page ") = false
m_page = 1
Else
m_page = cint (Request ("Page"))
END IF
M_PageSize = 14 'number of each page
SET cmd = server.createObject ("adodb.command")
cmd.activeConnection = conn
cmd.comMandType = 4
cmd.commandtext = "p_splitpage"
cmd.parameters.Append Cmd.createParameter ("@ SQL", 8, 1, 4000, SQL)
cmd.parameters.Append Cmd.createParameter ("@ Page", 4, 1, 4, m_page)
cmd.parameters.Append Cmd.createParameter ("@ Pagesize", 4, 1, 4, m_pagesize)
cmd.parameters.Append Cmd.createParameter ("@ pagecount", 4, 2, 4, m_pagecount)
CMD.Parameters.Append Cmd.createParameter ("@ recordcount", 4, 2, 4, m_recordcount)
SET RS = cmd.execute
SET RS = rs.nexTrecordset
m_pagecount = cmd.parameters ("@ pagecount"). Value
m_recordcount = cmd.parameters ("@ recordcount"). Value
IF m_pagecount = 0 THEN M_PAGECOUNT = 1
IF m_page> m_pagecount then
Response.Redirect ("BureAuser_Result.asp? Page =" & m_pagecount & "& lxsuser_name =" & lxsuser_name)
END IF
SET RS = cmd.execute
%>
| Query results A total of [8] page is the "Font Color =" # ff0000 "> [5] < / font> page |
|
