IF EXISTS (SELECT name FROM sysobjects WHERE id = object_id(N'a_Jboard_abstraction_S') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP proc a_Jboard_abstraction_S
GO

CREATE proc a_Jboard_abstraction_S
 @ji_num smallint,
 @ji_pagesize int,
 @reply_view int,
 @notice_view int,
 @ji_insert nchar(1) output,
 @ji_css nvarchar(70) output

AS

 Declare @SQL1 nvarchar(100)
 Declare @SQL2 nvarchar(100)
 Declare @SQL3 nvarchar(1600)
 Declare @ji_synchro nvarchar(1)
 Declare @ji_tname nvarchar(20)
 Declare @jb_sort nchar(5)
 Declare @a_var nvarchar(22)
 Declare @b_var nvarchar(18)

 SELECT @ji_synchro=ji_synchro, @ji_tname=ji_tname, @jb_sort=jb_sort FROM a_Jboard_info WHERE ji_sort='00' and ji_num = @ji_num

 set @SQL1 = 'SELECT @a=ji_css FROM a_Jboard_info WHERE ji_sort=''00'' and ji_num = '+ cast(@ji_num as nvarchar(5)) +' '
 set @a_var = '@a nvarchar(70) output'
 exec sp_executesql @SQL1, @a_var, @a=@ji_css output

 set @SQL2 = 'SELECT @b=ji_insert FROM a_Jboard_info WHERE ji_sort=''00'' and ji_num = '+ cast(@ji_num as nvarchar(5)) +' '
 set @b_var = '@b nchar(1) output'
 exec sp_executesql @SQL2, @b_var, @b=@ji_insert output

 set @SQL3 = 'SELECT TOP '+str(@ji_pagesize)+' '+ @ji_tname +'.jb_idx, '+ @ji_tname +'.jb_ref, '+ @ji_tname +'.jb_re_level, '
 set @SQL3 = @SQL3 + @ji_tname +'.jb_sort, '+ @ji_tname +'.jb_name, '+ @ji_tname +'.jb_title, '+ @ji_tname +'.jb_titleTag, '
 set @SQL3 = @SQL3 + @ji_tname +'.jb_readNum, '+ @ji_tname +'.jb_conNum, '+ @ji_tname +'.jb_CWDay, '+ @ji_tname +'.jb_link, '
 set @SQL3 = @SQL3 + @ji_tname +'.jb_file, '+ @ji_tname +'.jb_pixel, '+ @ji_tname +'.jb_down, '
 set @SQL3 = @SQL3 + @ji_tname +'.jb_etc, '+ @ji_tname +'.jb_writeday, '
 set @SQL3 = @SQL3 + 'a_Jboard_info.ji_name, a_Jboard_info.ji_tname '
 set @SQL3 = @SQL3 +'FROM '+ @ji_tname +' WITH(INDEX('+ @ji_tname +'_ListOrder)) INNER JOIN a_Jboard_info '
 set @SQL3 = @SQL3 +'ON '+ @ji_tname +'.jb_sort = a_Jboard_info.jb_sort '
 set @SQL3 = @SQL3 +'WHERE '+ @ji_tname +'.jb_report <> ''1'' '
 -- @ji_num는 게시판의 코드를 대입합니다.
  if (@ji_synchro = '0' or @ji_num >= 7 and @ji_num <= 9)
  begin
  set @SQL3 = @SQL3 +'and '+ @ji_tname +'.jb_sort = '+ @jb_sort +' '
  end
 else
  begin
  set @SQL3 = @SQL3 +'and '+ @ji_tname +'.jb_caNum = '+ cast(@ji_num as nvarchar(5)) +' '
  end
 set @SQL3 = @SQL3 +'and a_Jboard_info.ji_synchro <> ''0'' ' 
 if (@reply_view = 0) set @SQL3 = @SQL3 +'and '+ @ji_tname +'.jb_step = ''0'' '
 set @SQL3 = @SQL3 +'ORDER BY '
 if (@notice_view > 0) set @SQL3 = @SQL3 + @ji_tname +'.jb_notice DESC, '
 set @SQL3 = @SQL3 + @ji_tname +'.jb_ref DESC, '+ @ji_tname +'.jb_step ASC'
 exec(@SQL3)

GO