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 '音乐'
WHEN 1 THEN '电视'
WHEN 2 THEN '电影'
WHEN 3 THEN '卡通电影'
ELSE '未知'
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 '未知'
END,'未知'),
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