用存储过程创建视图

CREATE PROCEDURE sde.sp_CreateViewFor6Month
AS
declare
@tablename varchar(255),
@sql varchar(2000)
begin
/*******************
如果存在相同名称的视图，先把它删除
********************/
if exists (select * from dbo.sysobjects where id = object_id(N'[sde].[View_Inspection6Month]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [sde].[View_Inspection6Month]
/*******************
得到正确的表名
********************/
select @tablename = 'a' + convert(varchar(20),registration_id) from  SDE_table_registry where table_name='HS6M_LOCATION'
/*******************
组装SQL语句
********************/
set @sql = 'create view sde.View_Inspection6Month as '  +    
'SELECT sde.HSI.INSP_ID, sde.HSI.STRUCTURE_ID, sde.HSI.AREA, '+
      'sde.HSI.INSP_START_DATE, sde.HSI.INSP_END_DATE, sde.HSI.INSP_BY, '+
      'sde.HSI.DURATION, sde.HSI.HYD_STAFF_ID, sde.HSI.CONT_FREQ_ID, '+
      'sde.HSI.ACTUAL_FREQ_ID, sde.HSI.WEATHER_ID, sde.HSI.SUB_DATE, '+
      'sde.HSI.HAVE_EO, sde.HSI.HAVE_SSW, sde.HSI.DELETED,sde.HSI.COMP_DATE,sde.INSPECTION.WO_ID, '+
      'sde.INSPECTION.INSP_NO, sde.INSPECTION.FILE_REF, '+
      'sde.INSPECTION.DELETED AS Expr2, sde.STRUCTURE_LOCATION.TYPE,sde.HSI.LOCATION, sde.INSP_EO.EO_NO, '+
      'c.LOC_NO,c.ITEM_ID, c.COMM_ID,c.REP_D_DATE,  '+
      'c.REPAIR_ON, c.HAVE_WO_NO, '+
      'c.HANDLED_BY, c.DELETED AS Expr1 '+
'FROM sde.HSI LEFT OUTER JOIN '+