sp_helptext music_getGenre_sp
Select m_CD, m_GroupDesc From musicGenre_tbl
sp_helptext music_getChartList_sp
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
작성자 : 한영민
작성일 : 2003-10-13
설 명 : 장르 Select
--Sel_Mode
0 : 순위정렬
1 : 구매순 정렬
2 :선물순 정렬
3 : 프로포즈순 정렬
4 : 조르기 순 정렬
*/
ALTER Procedure music_getChartList_sp
@Sel_Mode INT = 0
AS
DECLARE @SQLSTRING NVARCHAR(500)
SET @SQLSTRING = ' Select mc_CD, mc_Subject, mc_artist, rank, dbo.rankMinusOldRank_udf(rank, mc_CD), buyCnt From musicContents_tbl '
IF(@Sel_Mode = 0) Begin --전체 검색
SET @SQLSTRING = @SQLSTRING + ' Order by rank Asc '
End
IF(@Sel_Mode = 1) Begin --구매순
SET @SQLSTRING = @SQLSTRING + ' Order by buyCnt Desc '
End
IF(@Sel_Mode = 2) Begin --선물순
SET @SQLSTRING = @SQLSTRING + ' Order by GiftCnt Desc '
End
IF(@Sel_Mode = 3) Begin --프로포즈순
SET @SQLSTRING = @SQLSTRING + ' Order by ProposeCnt Desc '
End
IF(@Sel_Mode = 4) Begin --조르기순
SET @SQLSTRING = @SQLSTRING + ' Order by AskToBuyCnt Desc '
End
Execute SP_ExecuteSQL @SQLSTRING
sp_helptext music_getUserAlbumOrBestAlbum_sp
exec music_getUserAlbumOrBestAlbum_sp 1
/*
작성자 : 한영민
작성일 : 2003-10-13
설 명 : 사용자 앨범 & 베스트 앨범 검색
*/
Alter Procedure music_getUserAlbumOrBestAlbum_sp
@Sel_Mode int = 1
AS
DECLARE @SQLSTRING NVARCHAR(500)
SET @SQLSTRING = ' Select ma_CD, ma_Title, ma_image, RegDate, UserID From musicAlbum_tbl '
--베스트 앨범 리스트
IF(@Sel_Mode = 1)
SET @SQLSTRING = @SQLSTRING + ' Where ma_BestReg = 1 and ma_BestAlbumActive = 1 '
--일반 앨범 리스트
IF(@Sel_Mode = 0)
SET @SQLSTRING = @SQLSTRING + ' Where ma_BestReg = 0 and ma_BestAlbumActive = 0 '
Execute SP_ExecuteSQL @SQLSTRING
sp_helptext music_bestAlbumContents_sp
/*
작성자 : 한영민
작성일 : 2003-10-13
설 명 : 오늘의 베스트 앨범 & 하위 TOP 5 수록곡
*/
Create Procedure music_bestAlbumContents_sp
@Sel_Mode INT
AS
Declare @ma_CD INT
Select Top 1 ma_CD = @ma_CD, ma_Title, ma_Content, ma_Image, regDate ,UserID
From musicAlbum_tbl
Where ma_BestReg = 1
and ma_BestAlbumActive = 1
and RegDate > Convert(Varchar(10), Getdate(), 20)
and RegDate < Convert(Varchar(10), DateAdd(day,1,Getdate()), 20)
Select Top 10 mar.mar_CD, mar.mc_CD, mar.UserID, mc.mc_Subject, mc.mc_Artist, mc.m_CD, m.m_GroupDesc
From musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
INNER JOIN musicGenre_tbl AS m ON m.m_CD = mc.m_CD
Where ma_CD = @ma_CD
IF @Sel_Mode=1 Begin --M_S_1 부분에서 호출
--최신앨범 여부 결정
Select NULL
End
/*
작성자 : 한영민
작성일 : 2003-10-15
설 명 : 나의 앨범 현황예 따른 수록곡 리스트
*/
Create Procedure music_UserAlbumContents_sp
@mc_CD INT
AS
Select rank, mc_Subject, mc_Artist
From musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
Where mar.mc_CD = @mc_CD
Alter Table musicAlbum_tbl
Drop Column ma_BesContent
sp_helptext music_setAlbumTOBestAlbum
/*
작성자 : 한영민
작성일 : 2003-10-14
설 명 : 베스트 앨범으로 신청하기
*/
ALTER Procedure dbo.music_setAlbumTOBestAlbum
@ma_BestReg INT = 0
, @ma_CD INT
, @ma_BestTitle NVarchar(50)
, @ma_BestContent NVarchar(200)
AS
IF @ma_BestReg = 0 Begin
Return 0
End
Update musicAlbum_tbl Set ma_BestReg = @ma_BestReg, ma_BestTitle = @ma_BestTitle, ma_BestContent = @ma_BestContent Where ma_CD = @ma_CD
IF @@Error <> 0 Begin
GOTO Err
End
Return 1
Err:
Return 0
Execute dbo.music_setAlbumTOBestAlbum
@ma_BestReg = 1
, @ma_CD = 1
, @ma_BestTitle = '나의 첫 베스트 앨번'
, @ma_BestContent = '흐흐흐'
insert musicAlbum_tbl(ma_title, ma_Content, ma_image, ma_BestReg, ma_BestAlbumActive,UserID, ma_BestTitle, ma_BestContent) values('Vins Album', '사연있는 곡 모음', '', 0, 0, 'icdtl', '','')
Select * from musicAlbum_tbl
Update musicAlbum_tbl Set ma_BestReg = 0, ma_BestTitle = '', ma_BestContent = '' Where ma_CD =1
Select @@RowCount
