CREATE PROCEDURE AAAAAAAAAAAAAAA
int,
--改頁条件
@strChangePage nvarchar(100),
--印刷項目
@strPrintRight nvarchar(1000),
--条件
@resql nvarchar(2000),
--入学手続区分
@nyugakuKbn varchar(200)
varchar(8000) output
AS
set nocount on
declare @sortName varchar(100)
declare @strItem varchar(100)
declare @code varchar(100)
declare @name varchar(100)
declare @i int
declare @sqlFrom varchar(8000)
declare @tempsql varchar(8000)
declare @tempIndex int
declare SortListCursor Cursor For
select * from dbo.f_split(@strChangePage, ',')
if @nameFlag=1
begin
exec('declare NyugakuKbn Cursor For SELECT コード,略称 as 名称 FROM コード区分マスタ WHERE 区分識別コード = 5 and コード )
end
else
begin
exec('declare NyugakuKbn Cursor For SELECT コード,名称 FROM コード区分マスタ WHERE 区分識別コード = 5 and コード )
end
if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..##MiTetuduki') and xtype='U')
drop table ##MiTetuduki
create table ##MiTetuduki(id int,data varchar(8000))
set @tempsql=''
set @tempIndex=0
--基本情報をクエリー
declare PrintRightCursor Cursor For
select * from dbo.f_split(@strPrintRight, ',')
Open PrintRightCursor
FETCH PrintRightCursor INTO @sortName
WHILE @@FETCH_STATUS = 0
BEGIN
if (@sortName= '性別')
begin
set @tempsql = ' CASE b.性別 WHEN ''1'' THEN ''女'' ELSE ''男'' END as 性別,'
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if (@sortName= '出身校')
begin
if @nameFlag=1
begin
set @tempsql = ' CASE WHEN ISNULL(b.出身校ID,'''') ='''' THEN ISNULL(b.出身校名,'''') ELSE ISNULL(d.学校名略称,'''') END as
end
else
begin
set @tempsql = ' CASE WHEN ISNULL(b.出身校ID,'''') ='''' THEN ISNULL(b.出身校名,'''') ELSE ISNULL(d.学校名,'''') END as
end
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if(@sortName= '特待生区分')
begin
if @nameFlag=1
begin
set @tempsql = ' ISNULL(e.特待生略称,'''') as
end
else
begin
set @tempsql = ' ISNULL(e.特待生名,'''') as
end
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if(@sortName= '入試区分')
begin
if @nameFlag=1
begin
set @tempsql = ' ISNULL(f.入試略称,'''') as
end
else
begin
set @tempsql = ' ISNULL(f.入試名称,'''') as
end
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if(@sortName= '入学手続区分')
begin
set @i=0
Open NyugakuKbn
FETCH NyugakuKbn INTO @code,@name
WHILE @@FETCH_STATUS = 0
BEGIN
set @tempsql =' case when a.特待生区分 is not null then '
+' case when exists(SELECT 手続 AS 手続 FROM 手続設定マスタ'
+' where 特待生コード =a.特待生区分 AND 手続区分コード
+''' and 手続=''-'') then ''-'' else ISNULL(CAST(DATEPART(m, m'+cast(@i as varchar(10))
+'.完了日付) AS varchar(2))+''/'' + CAST(DATEPART(d,m'+cast(@i as varchar(10))
+'.完了日付) AS varchar(2)),'''') end '
+' else '
+' case when exists( SELECT 属性値２ AS 手続 FROM コード区分マスタ '
+' where 区分識別コード =5 AND コード
+''' and 属性値２ =''-'') then '''' else '
+'ISNULL(CAST(DATEPART(m, m'+cast(@i as varchar(10))
+'.完了日付) AS varchar(2))+''/''+CAST(DATEPART(d, m'+cast(@i as varchar(10))
+'.完了日付) AS varchar(2)),'''') end end as [入学手続区分;'+@name+'],'
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @i=@i+1
set @tempIndex=@tempIndex+1
FETCH NyugakuKbn INTO @code,@name
END
CLOSE NyugakuKbn
end
else
begin
set @tempsql=@sortName+ ' as ['+@sortName+'],'
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
FETCH PrintRightCursor INTO @sortName
END
CLOSE PrintRightCursor
DEALLOCATE PrintRightCursor
-----------------------------
--set @str + ' a.システム受験生ID as ID, '
--改頁条件
Open SortListCursor
FETCH SortListCursor INTO @sortName
WHILE @@FETCH_STATUS = 0
BEGIN
if(@sortName='入試区分')
begin
if @nameFlag=1
begin
set @tempsql = ' ISNULL(f.入試略称,'''') as 入試区分名,'
end
else
begin
set @tempsql = ' ISNULL(f.入試名称,'''') as 入試区分名,'
end
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if (@sortName='出身校')
begin
if @nameFlag=1
begin
set @tempsql = ' CASE WHEN ISNULL(b.出身校ID,'''') ='''' THEN ISNULL(b.出身校名,'''') ELSE ISNULL(d.学校名略称,'''') END as 出身校名,'
end
else
begin
set @tempsql = ' CASE WHEN ISNULL(b.出身校ID,'''') ='''' THEN ISNULL(b.出身校名,'''') ELSE ISNULL(d.学校名,'''') END as 出身校名,'
end
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if (@sortName='受験番号順')
begin
set @tempsql='a.受験番号 as 受験番号順,'
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if (@sortName='氏名順')
begin
set @tempsql='b.氏名 as 氏名順,'
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if (@sortName='出身校順')
begin
if @nameFlag=1
begin
set @tempsql = ' CASE WHEN ISNULL(b.出身校ID,'''') ='''' THEN ISNULL(b.出身校名,'''') ELSE ISNULL(d.学校名略称,'''') END as 出身校名,'
end
else
begin
set @tempsql = ' CASE WHEN ISNULL(b.出身校ID,'''') ='''' THEN ISNULL(b.出身校名,'''') ELSE ISNULL(d.学校名,'''') END as 出身校名,'
end
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
else if (@sortName='特待生区分')
begin
set @tempsql='a.特待生区分 as 特待生区分順,'
insert into ##MiTetuduki (id,data) values(@tempIndex,@tempsql)
set @tempIndex=@tempIndex+1
end
FETCH SortListCursor INTO @sortName
END
CLOSE SortListCursor
DEALLOCATE SortListCursor
-------------------------------
set @sqlFrom = ' from 受験生基本情報 a left join 受験生個人情報 b on a.システム受験生ID=b.システム受験生ID '
+' left join 受験生入学手続情報 c on a.システム受験生ID=c.システム受験生ID '
+' left join 出身校マスタ d ON b.出身校ID = d.学校コード '
+' left join 特待生マスタ e on a.特待生区分=e.特待生コード '
+' left join 入試区分マスタ f on a.入試区分=f.入試区分コード '
set @i=0
Open NyugakuKbn
FETCH NyugakuKbn INTO @code,@name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlFrom LEFT JOIN 受験生入学手続情報 m'+cast(@i as varchar(10))
+' ON a.システム受験生ID=m'+cast(@i as varchar(10))
+'.システム受験生ID AND m'+cast(@i as varchar(10))
+'.手続区分コード
set @i=@i+1
FETCH NyugakuKbn INTO @code,@name
END
CLOSE NyugakuKbn
DEALLOCATE NyugakuKbn
set @sqlFrom WHERE 1 =1 '
-----------------------------------------------------------------------------
update ##MiTetuduki set data=left(data,len(data)-1) where
declare @sqlhead varchar(8000),@sqlend varchar(8000)
,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
,@ic varchar(20),@count int
--
select id=identity(int,0,1),gid=0
,a=data
into # from ##MiTetuduki a
set @count=0
set @resql=replace(@resql,'''','''''')
set @sqlFrom=replace(@sqlFrom,'''','''''')
select @sqlhead= ''' select distinct '''
,@sqlend=''''+@sqlFrom+@resql+''''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
while @count<
select @ic=cast(@count as varchar),@count=@count+1
,@sql1='@'+@ic+' varchar(8000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where
+char(13)+@sql3
,@sql4=@sql4+'+@'+@ic
select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,8000)
------------------------------------------------------------------------------------------
--print @sql1+@sql2+@sql3
--print(@sqlhead+@sql4+@sqlend)
--select * from ##MiTetuduki
exec ( @sql1+@sql2+@sql3+' exec()
drop table #
drop table ##MiTetuduki
