set QUOTED_IDENTIFIER ON
go
--------------------------------------------------------
CREATE PROC [dbo].[up_main_update]
AS
SET NOCOUNT ON
declare @totalcount int, @chk_num int
CREATE TABLE #Notice(
[idx] [int] IDENTITY(1,1) NOT NULL,
[Gubun] [nvarchar] (10) NULL,
[seq] [int] NULL,
[Title] [nvarchar](50) COLLATE Korean_Wansung_CI_AS NULL,
[RegDate] [nvarchar] (10) NULL
)
insert into #Notice select gubun, seq, title, regdate from
(
select top 3 'notice' as 'gubun', seq,title,right(replace(convert(varchar(10),regdate,111),'/','.'),8) as 'regdate'
from notice where seq in (select top 5 seq from notice where status = '1' and len(title) > 0 order by rank desc,seq desc )
union
select top 2 'event' as 'gubun', seq,title,right(replace(convert(varchar(10),regdate,111),'/','.'),8) as 'regdate'
from event where seq in (select top 5 seq from event where status = '1' and len(title) > 0 order by seq desc )
) as a order by regdate desc
set @totalcount = @@rowcount
set @chk_num = 0
while(@totalcount < 5)
begin
if(@chk_num = 0 and @totalcount = 0 )
begin
--insert into #Notice select 'notice' as 'gubun', '0','등록된 자료가 없습니다.',right(replace(convert(varchar(10),getdate(),111),'/','.'),8) as 'regdate'
insert into #Notice select 'notice' as 'gubun', '0','undefined', 'undefined' as 'regdate'
end
else
begin
-- insert into #Notice select 'notice' as 'gubun', '0','undefined',right(replace(convert(varchar(10),getdate(),111),'/','.'),8) as 'regdate'
insert into #Notice select 'notice' as 'gubun', '0','undefined', 'undefined' as 'regdate'
end
print @totalcount
set @totalcount = @totalcount + 1
set @chk_num = @chk_num + 1
end
select * from #Notice order by idx
drop table #Notice
--------------------------------------------------------
Posted by 홍반장
