So it's best to page through the data, if this is the use of stored procedures to pagination. The following is a stored procedure for data paging using the employee table in the pubs database, which you can refer to to create your own stored procedures based on the actual situation.
Note: @pageindex the index of the data page, @dataperpage the number of records per page, @howmanyrecords to get the total number of records.
Copy Code code as follows:
create proc GetData @pageindex int, @dataperpage int, @howmanyrecords int output
As
DECLARE @temptable table
(
rowindex int,
emp_id Char (9),
fname varchar (20),
Minit char (1),
lname varchar (30)
)
INSERT INTO @temptable
Select Row_number () over (emp_id) as Rowindex,emp_id,fname,minit,lname
From employee
Select @howmanyrecords =count (rowindex) from @temptable
SELECT * FROM @temptable
where Rowindex> (@pageindex-1) * @dataperpage
and rowindex<= @pageindex * @dataperpage
DECLARE @howmanyrecords int
exec getdata 2,5, @howmanyrecords output
Select @howmanyrecords
declare @x int, @y int, @z int
Select @x = 1, @y = 2, @z=3
Select @x,@y,@z
create proc getdata2 @pageindex int, @dataperpage int, @howmanyrecords int output
As
DECLARE @temptable table
(
rowindex int,
emp_id Char (9),
fname varchar (20),
Minit char (1),
lname varchar (30)
)
INSERT INTO @temptable
Select Row_number () over (emp_id) as Rowindex,emp_id,fname,minit,lname
From employee
Select @howmanyrecords =count (rowindex) from @temptable
SELECT * FROM @temptable
where Rowindex> (@pageindex-1) * @dataperpage
and rowindex<= @pageindex * @dataperpage
Where the Row_number function can number each retrieved record by sort.
You can then call the stored procedure in the ASP.net Web page background code to get the data you want.
