/*
작성자 : 한염민
작성일 : 2003-10-31
설 명 : 배경음악 선곡
*/
ALTER Procedure dbo.music_BgSound_sp
@mar_CD INT = 0
, @ReturnMsg NVarchar(20) Output
AS
IF @mar_CD = 0 Begin
Goto Err
End
Declare @isBool INT, @LimitCount INT, @UserID NVarchar(12)
SET @isBool = 0
SET @LimitCount = 0
SET @UserID = ''
Select @UserID = UserID From musicAlbumRecord_tbl Where mar_CD = @mar_CD
IF @UserID='' Or @UserID is null Begin
Set @ReturnMsg = ' 보유음악에 대한 권한이 없습니다.'
Goto Err
End
Select @LimitCount =Count(*) from musicBgSound AS mbs INNER JOIN musicAlbumRecord_tbl AS mar
ON mbs.mar_CD = mar.mar_CD
Where mar.UserID = @UserID
IF @LimitCount >=10 Begin
Set @ReturnMsg = '배경음악은 10곡을 넘길수 없습니다.'
Goto Err
End
Select @isBool = Count(mar_CD) From musicBgSound Where mar_CD = @mar_CD
IF @isBool > 0 Begin
Set @ReturnMsg = '이미 선곡한 배경음악 입니다.'
Goto Err
End
INSERT musicBgSound(mar_CD) Values(@mar_CD)
iF @@Identity > 0 Begin
Set @ReturnMsg = '배경 음악 선곡 완료'
Return 1
End
Else Begin
Set @ReturnMsg = '네트워크 장애 또는 DataBase 문제 입니다.'
Goto Err
End
Err:
Return 0
/*
작성자 : 한영민
작성일 : 2003-10-06
설 명 : 새로 배경음악을 지정할때 기존 곡을 삭제하고 다시 담기로 정책 변경
*/
CREATE Procedure dbo.music_del_BgSound_sp
@UserID NVarchar(12) = 'system'
AS
IF @UserID = 'system' Goto Err
Delete from musicBgSound Where mar_CD IN (
Select mar_CD From musicAlbumRecord_tbl
Where UserID = @UserID)
Return 1
Err:
Return 0
Select * from musicBgSound AS mbs INNER JOIN musicAlbumRecord_tbl AS mar
ON mbs.mar_CD = mar.mar_CD
Where mar.UserID = 'icdtl'
SP_HELPTEXT music_bestAlbumContents_sp
/*
작성자 : 한영민
작성일 : 2003-10-13
설 명 : 오늘의 베스트 앨범 & 하위 TOP 5 수록곡
*/
CREATE Procedure music_bestAlbumContents_sp
@Sel_Mode INT = 1
AS
Declare @ma_CD INT
Select Top 1 ma_CD , ma_Title, ma_Content, ma_Image, Convert(Varchar(10), regDate, 21) AS regDate ,UserID
From musicAlbum_tbl
Where ma_BestReg = 1 and ma_BestAlbumActive = 1
Order by regDate desc
--and RegDate > Convert(Varchar(10), Getdate(), 20)
--and RegDate < Convert(Varchar(10), DateAdd(day,1,Getdate()), 20)
Select Top 1 @ma_CD = ma_CD From musicAlbum_tbl
Where ma_BestReg = 1 and ma_BestAlbumActive = 1
Order by regDate desc --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, mc.file_code
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 aid, Convert(nVarchar(20), album_explain) AS album_explain, album_name, img_name From musicCityAlbum Where album_theme = 1 and active = 1 Order by signdate desc
End
sp_helptext music_AlbumBuy_sp
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
작성자 : 한영민
작성일 : 2003-10-27
설 명 : 앨범 리스트
*/
CREATE PROCEDURE dbo.music_AlbumBuy_sp
@aid INT = 0
, @ma_CD INT = 0
AS
IF @aid <> 0 Begin
Select aid, artist_name, img_name, abuyCnt, price
From musicCityAlbum
Where aid = @aid and active = 1
Select file_code, song_subject, price, sellprice, discountRate from musicCityContents
Where album_uid = @aid
End
IF @ma_CD <> 0 Begin
Select ma_CD, ma_Title, ma_image
From musicAlbum_tbl
Where ma_CD = @ma_CD
Select mc.file_code, mc.mc_subject, mcc.price, mcc.sellprice, mcc.discountRate from dbo.musicAlbumRecord_tbl AS mar
INNER JOIN musicContents_tbl AS mc ON mar.mc_CD = mc.mc_CD
INNER JOIN musicCityContents AS mcc ON mc.file_code = mcc.file_code
Where ma_CD = @ma_CD
End
IF @@Error != 0 Begin
Goto Err
End
Return
Err:
Select Null
Return
Select *
From musicCityAlbum
Where aid = 59 and active = 1
Select file_code, song_subject, price, sellprice, discountRate from musicCityContents
Where file_code in
Select song_list From musicCityAlbum
Where aid = 59 and active = 1
Select * from musicCityContents
Where file_code in(
96950.96951,96952,96953,96954,96955,96956,96957,96958,96959,96960,96961,96962,96963,96964,96965
)
Select * from musicCityContents
