动态生成视图

/*
date:2007-2-11
creater: sanle
desc:生成视图过程
*/

ALTER    proc ccyt_update
(
@xmbh varchar(20),
@year varchar(4),
@bz int
)
as

declare @sql varchar(8000) --sql语句
declare @tbname varchar(100)--表名称
declare @tbother varchar(100)--表名称
declare @dwjs varchar(100)  --段位解释
declare @sm varchar(10)     --说明
if @bz=1
set @sm='备用'
else
set @sm='航站'

set @year=left(ltrim(rtrim(@year)),4)
set @xmbh=ltrim(rtrim(@xmbh))

set @dwjs='xt_dwjs_'+ @xmbh + @year
--转pzb
set @tbname=''
set @tbother=''
set @tbname='xt_pzbb_' + @xmbh + @year
set @tbother='xt_pzb_' + @xmbh + @year

if  not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tbname+']')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin
 set @sql='create view ' + @tbname + ' as SELECT a.*, b.DESCRIPTION AS l1, c.DESCRIPTION AS l2,m3 as l3, d.DESCRIPTION AS l4, e.DESCRIPTION AS l5, f.DESCRIPTION AS l6, g.DESCRIPTION AS l7, h.DESCRIPTION AS l8, i.DESCRIPTION AS l9
FROM ((((((((
'+ @tbother + ' 
AS a LEFT JOIN
'+ @dwjs + '
 AS b ON a.s1 = b.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
AS c ON a.s2 = c.FLEX_VALUE)
LEFT JOIN
'+ @dwjs + '
 AS d ON a.s4 = d.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS e ON a.s5 = e.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS f ON a.s6 = f.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS g ON a.s7 = g.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS h ON a.s8 = h.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS i ON a.s9 = i.FLEX_VALUE)
 WHERE (((b.SEGMENT_NAME) Like ''%公司%'') AND ((c.SEGMENT_NAME) Like ''%责任中心%'') AND ((d.SEGMENT_NAME) Like ''%子目%'') AND ((e.SEGMENT_NAME) Like ''%参考%'') AND ((f.SEGMENT_NAME) Like ''%机型%'') AND ((g.SEGMENT_NAME) Like ''%机号%'') AND ((h.SEGMENT_NAME) Like ''%航线%'') AND ((i.SEGMENT_NAME) Like ''%'+@sm+'%'')) '
 
       print (@sql)
       exec (@sql)
end

set @tbname=''
set @tbother=''
set @tbname='xt_gh_zzz_' + @xmbh + @year
set @tbother='xt_gh_zz_' + @xmbh + @year
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tbname+']')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin
 set @sql='create view ' + @tbname + ' as SELECT a.*, b.DESCRIPTION AS l1, c.DESCRIPTION AS l2, d.DESCRIPTION AS l4,m3 as l3, e.DESCRIPTION AS l5, f.DESCRIPTION AS l6, g.DESCRIPTION AS l7, h.DESCRIPTION AS l8, i.DESCRIPTION AS l9
FROM ((((((((
'+ @tbother + ' 
AS a LEFT JOIN
'+ @dwjs + '
 AS b ON a.s1 = b.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
AS c ON a.s2 = c.FLEX_VALUE)
LEFT JOIN
'+ @dwjs + '
 AS d ON a.s4 = d.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS e ON a.s5 = e.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS f ON a.s6 = f.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS g ON a.s7 = g.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS h ON a.s8 = h.FLEX_VALUE) LEFT JOIN
'+ @dwjs + '
 AS i ON a.s9 = i.FLEX_VALUE)
 WHERE (((b.SEGMENT_NAME) Like ''%公司%'') AND ((c.SEGMENT_NAME) Like ''%责任中心%'') AND ((d.SEGMENT_NAME) Like ''%子目%'') AND ((e.SEGMENT_NAME) Like ''%参考%'') AND ((f.SEGMENT_NAME) Like ''%机型%'') AND ((g.SEGMENT_NAME) Like ''%机号%'') AND ((h.SEGMENT_NAME) Like ''%航线%'') AND ((i.SEGMENT_NAME) Like ''%'+@sm+'%'')) '
        print (@sql)
 exec (@sql)
end

/*
exec ccyt_update '101100101','2005',1
*/