A paging solution using temporary tables

Create proc bmhd_user_getconsumebyuserid
@ Userid int,
@ Pageindex int,
@ Pagesize int,
@ Recordcount int
As

Declare
@ Pagelowerbound int,
@ Pageupperbound int,
@ Rowstoreturn int

Set @ pagelowerbound = @ pagesize * @ pageindex
Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1
Set @ rowstoreturn = @ pagesize * (@ pageindex + 1)
Set rowcount @ rowstoreturn

Create Table # pageindex
(
Indexid int identity (1, 1) not null,
Oid int
)

Insert into # pageindex (OID) Select ID from userconsumelog where uid = @ userid order by ID DESC

Create Table # result
(
Id int,
UID int,
Mediatype nvarchar (20 ),
Medianame nvarchar (500 ),
Money int,
Donetime datetime
)

Insert into # result
Select
U. ID,
U. uid,
Case mediatype
When 0 then 'music'
When 1 then 'TV'
When 2 then 'cine'
When 3 then 'cartoon files'
Else 'unknown'
End,

Isnull (Case mediatype
When 0 then (select top 1 S. songname from Song s where S. songid = U. mediaid)
When 1 then (select top 1 T. tvname + '-' + ltrim (STR (Tu. tvnumber) from TV T, tvurl tu where u. mediaid = tu. tvurlid and T. tvid = tu. tvid)
When 2 then (select top 1 m. moviename + '-' + ltrim (STR (Mu. movienumber) from movie M, movieurl Mu where u. mediaid = Mu. movieurlid and M. movieid = Mu. movieid)
When 3 then (select top 1 cm. cartoonmoviename + '-' + ltrim (STR (CMU. cartoonmovienumber) from cartoonmovie cm, cartoonmovieurl CMU where u. mediaid = CMU. cartoonmovieurlid and CM. cartoonmovieid = CMU. cartoonmovieid)
Else 'unknown'
End, 'unknown '),

U. Money,
U. donetime
From
Userconsumelog U,
# Pageindex P
Where
U. ID = P. OID and
P. indexid> @ pagelowerbound and
P. indexid <@ pageupperbound

Select * from # result order by ID DESC

If @ recordcount <0 begin
Select count (*) from userconsumelog where uid = @ userid
End
Go