Example of implementing pagination-using stored procedures to implement pagination

Source: Internet
Author: User
Tags goto implement
There are many programs that discuss how to implement pagination on the web, and I'm here to introduce a new way to implement pagination, using stored procedures to implement pagination
Because this program is written earlier, at that time did not have SQL 7, each Varchar can only support 255 characters, so take a relatively stupid way, if you are interested, please go to http://www.chinaasp.com/sqlbbs/default.a SP's Database forum comments, I will look at the situation, decide whether to modify this stored procedure into SQL 7 stored procedures;
And to play a role in this.
This program can only reach 10 paging
if exists (select * from sysobjects where id = object_id (' dbo.sp_productpage ') and Sysstat & 0xf = 4)
drop procedure Dbo.sp_productpage
Go

CREATE PROCEDURE Sp_productpage
@intStart Tinyint=1,
@intEnd tinyint=10
With encryption
As
Declare @strProductID VARCHAR (8), @strProductName VARCHAR (20),
@strSQL1 VARCHAR (100),
@strSQL2 VARCHAR (100),
@strSQL3 VARCHAR (100),
@strSQL4 VARCHAR (100),
@strSQL5 VARCHAR (100),
@strSQL6 VARCHAR (100),
@strSQL7 VARCHAR (100),
@strSQL8 VARCHAR (100),
@strSQL9 VARCHAR (100),
@strSQL10 VARCHAR (100),
@intCCount TINYINT,
@intCount TINYINT,
@i TINYINT
Select @i=1
Declare cur_product SCROLL CURSOR for
Select Productid,productname from Kf_product order by ProductID
Select @intCCount =count (productId) from kf_product
Open Cur_product
Fetch absolute @intStart cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Select @intCount = @intStart
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL1 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL1 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL2 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL2 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL3 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL3 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL4 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL4 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL5 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL5 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL6 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL6 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL7 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL7 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL8 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL8 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL9 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount) + ' Union '
End
Else
Begin
Select @strSQL9 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conve RT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Fetch cur_product into @strProductID, @strProductName
If @ @FETCH_STATUS =0
Begin
Select @intCount = @intCount +1
Select @strSQL10 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conv ERT (VARCHAR, @intCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
End
Else
Begin
Select @strSQL10 = ' Select productid= ' + @strProductID + ', productname= ' + @strProductName + ', productcount= ' + conv ERT (VARCHAR, @intcCount) + ', productsumcount= ' + convert (VARCHAR, @intCCount)
Goto Endpro
End
Endpro:
Close Cur_product
Deallocate cur_product
Print @strSQL1
Print @strSQL2
Print @strSQL3
EXEC (@strSQL1 + @strSQL2 + @strSQL3 + @strSQL4 + @strSQL5 + @strSQL6 + @strSQL7 + @strSQL8 + @strSQL9 + @strSQL10)
Go
Once the stored procedure has been successfully created, you can make the following calls in the ASP
Strsql= "Sp_productpage 1,10
Rst.open strsql,conn,3,1
It's okay.




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.