Select * from dbo.musicAlbum_tbl
update musicAlbum_tbl set ma_bestreg = 1 , ma_BestAlbumActive = 1 , regDAte = getdate() Where ma_CD < 13
sp_helptext music_bestAlbumContents_sp
Select * from musicCityAlbum
update musicCityAlbum set album_theme = 2 Where aid < 9
execute music_ThemaAlbumContents_sp 2
sp_helptext music_ThemaAlbumContents_sp
Select aid, album_name, img_name, abuyCnt
From musicCityAlbum
music_getUserAlbumOrBestAlbum_sp
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
작성자 : 한영민
작성일 : 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, ma_Content, UserID, Pro_Age, Pro_Gender, AREA From music_UserAlbumList_v '
--베스트 앨범 리스트
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
/*
작성자 : 한영민
작성일 : 2003-10-22
설 명 : 뮤직 앨범(역대베스트)등에서 호출하는 정보 + 사용자 기초 정보(UDF사용)
*/
CREATE VIEW dbo.music_UserAlbumList_v
AS
Select ma_CD, ma_Title, ma_image, RegDate, ma_Content, ma_BestReg, ma_BestAlbumActive, UserID, Pro_Age, meeting.dbo.fn_GetGender(Pro_Gender) AS Pro_Gender, meeting.dbo.fn_GetUserDetail(SubString(Pro_Profile,2,1)) AS AREA
From musicAlbum_tbl AS ma INNER JOIN Meet_Profile AS mp ON ma.UserID=mp.Pro_ID
Select From Meet_Profile Where Pro_ID = 'icdtl'
Select from Meet_Profile Where Pro_ID = 'icdtl'
CREATE Function dbo.fn_GetUserDetail
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(5)
BEGIN
DECLARE @RX VARCHAR(5)
IF @SEQ ='A' SET @RX = '서울'
IF @SEQ ='B' SET @RX = '경기'
IF @SEQ ='C' SET @RX = '인천'
IF @SEQ ='D' SET @RX = '부산'
IF @SEQ ='E' SET @RX = '대전'
IF @SEQ ='F' SET @RX = '대구'
IF @SEQ ='G' SET @RX = '광주'
IF @SEQ ='H' SET @RX = '울산'
IF @SEQ ='I' SET @RX = '강원'
IF @SEQ ='J' SET @RX = '충북'
IF @SEQ ='K' SET @RX = '충남'
IF @SEQ ='L' SET @RX = '경남'
IF @SEQ ='M' SET @RX = '경북'
IF @SEQ ='N' SET @RX = '전북'
IF @SEQ ='O' SET @RX = '전남'
IF @SEQ ='P' SET @RX = '제주'
IF @SEQ ='Q' SET @RX = '해외'
RETURN (@RX)
END
drop view musicTransferList_v
/*
작성자 : 한영민
작성일 : 2003-10-22
설 명 : 팝업프로실에서 받음, 보냄, 희망, 추천음악함의 뷰
*/
CREATE VIEW dbo.musicTransferList_v
AS
Select mt_CD, mt.mc_CD, mt_PrimaryUserID, mt_ForeignUserID, Price, SellPrice, DisCountRate, mt_Give, mt_Take, mt_Wish, mt_Recm, mc.mc_Subject, mc.mc_Artist, mc.file_code
From musicTransferList AS mt
INNER JOIN musicContents_tbl AS mc ON mt.mc_CD = mc.mc_CD
INNER JOIN musicCityContents AS mcc ON mc.file_code = mcc.file_code
