Tutorials to .com

Tutorials to .com » Asp » Database » Stored procedure on page

Stored procedure on page

Print View , by: iSee ,Total views: 12 ,Word Count: 864 ,Date: Sun, 24 May 2009 Time: 4:12 PM

Saw a few friends to write stored procedures on the article page, feeling a little problem. Spider starleee and eastern wish I could express views, to put it simply click.

The first is that the paging method allsky do not set up to see if he is like this:
select @ iStart = (@ iPage-1) * @ iPageSize
select @ iEnd = @ iStart + @ iPageSize +1
In other words, the beginning and end of his per page id in accordance with the calculated number of hardware, you want this method to set up a condition that must be met so that there is only one page of this forum and id a row starting at 1, and the middle can not be There are intervals, that is the case ifit will go wrong.

Followed by starleee that, in fact, way of thinking is correct, but since both used for paging id method, there is no need to use the cursor, you can use select top * or set rowcount = syntax in order to obtain both the id, the first method only can be used in the sql server, the latter in sybase and oracle can be set up in.
tested starleee mentioned that this method faster than using the cursor, in fact, the problem in his index built properly, the absence of a specific index of the stored procedure. The efficiency of the impact of the database is the largest factor in the index, where there is a need to talk about. If a theory of the first field to sort the index could not filter out most of the data, this index is not appropriate, this may be some obscure, for example, according to an example:
select id, name, forumid from tablexxx where forumid = 1 and name like '% aaa%' order by id
Look at the top of this statement, if you want efficient, it requires the establishment of such an index:
forumid, id
Say to, if there are one million in a record table with this statement, if we do not build the index, the biggest might be to overtime, and the establishment of the above-mentioned index, if the records meet the conditions, it can be 1 seconds to respond (to elect the first record of compliance with the conditions), and if the record does not meet the conditions, you can respond in one minute.

The following is my stored procedure to use for both bbs method id of the page, we can look at
/************************************************* ************************/
/ * * /
/ * Procedure: up_GetTopicList * /
/ * * /
/ * Description: post list * /
/ * * /
/ * Parameters: @ a_intForumID: space id * /
/ * @ A_intPageNo: page number * /
/ * @ A_intPageSize: a few per page, whichever is affixed to the root * /
/ * * /
/ * Use table: bbs, forum * /
/ * * /
/ * Author: bigeagle@163.net * /
/ * * /
/ * Date: 2000/2/14 * /
/ * * /
/ * History: * /
/ * * /
/************************************************* ************************/
if exists (select * from sysobjects where id = object_id ( 'up_GetTopicList'))
drop proc up_GetTopicList
go

create proc up_GetTopicList
@ a_intForumID int,
@ a_intPageNo int,
@ a_intPageSize int
as
/ * Definition of local variables * /
declare @ intBeginID int
declare @ intEndID int
declare @ intRootRecordCount int
declare @ intPageCount int
declare @ intRowCount int
/ * Close the count * /
set nocount on

/ * Detect whether there is such a layout * /
if not exists (select * from forum where id = @ a_intForumID)
return (-1)

/ * Root paste for a total number of * /
select @ intRootRecordCount = count (*) from bbs where fatherid = 0 and forumid = @ a_intForumID
if (@ intRootRecordCount = 0) - If there are no postings, then return to zero
return 0

/ * Determine the number of pages is correct * /
if (@ a_intPageNo - 1) * @ a_intPageSize> @ intRootRecordCount
return (-1)

/ * Seek to start rootID * /
set @ intRowCount = (@ a_intPageNo - 1) * @ a_intPageSize + 1
/ * Limit the number of rules * /
set rowcount @ intRowCount
select @ intBeginID = rootid from bbs where fatherid = 0 and forumid = @ a_intForumID
order by id desc

/ * End of rootID * /
set @ intRowCount = @ a_intPageNo * @ a_intPageSize
/ * Limit the number of rules * /
set rowcount @ intRowCount
select @ intEndID = rootid from bbs where fatherid = 0 and forumid = @ a_intForumID
order by id desc

/ * To restore the system variables * /
set rowcount 0
set nocount off

select a.id, a.layer, a.forumid, a.subject, a.faceid, a.hits, a.time, a.UserID, a.fatherid, a.rootid,
'Bytes' = datalength (a.content), b.UserName, b.Email, b.HomePage, b.Signature, b.Point
from bbs as a join BBSUser as b on a.UserID = b.ID
where Forumid = @ a_intForumID and a.rootid between @ intEndID and @ intBeginID
order by a.rootid desc, a.ordernum desc
return (@ @ rowcount)
- select @ @ rowcount
go


ASP Database Related Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.