关于用存储过程中动态创建视图的一点心得。

来源：互联网发布：手机视频导入mac找不到编辑：程序博客网时间：2024/05/21 02:49

        工作中遇到的问题，要不然也不会想到要去如何实现了。本来已经创建好了视图，后台的代码也写完了，突然得到指示，其中一张比较重要的表的表名会因为在不同的PC上安装数据库而发生变化（在另一张表中可以根据规则查询到对应的正确的表名），所以，想个办法解决吧。
        第一感觉，郁闷！然后，是思考策略，反正大的前提是不想改代码，所以，只能从后台下手了，最好的解决办法就是利用存储过程动态创建视图，这样就和代码没什么太大关系了（只要在合适的地方调用存储过程创建视图而已，两行代码就可以搞定）。
        开始考虑可行性，以前从来没想过这个问题，如果无法实现，那也只能放弃。上网查资料，没有找到相关的东西。只能自己动手写一个存储过程试试看了。想把表名作为参数传递给存储过程，在实践过程中发现，如果想写成dbo.@tablename.XXX字段来取得动态表字段的话，是不可行地，SQL不认啊。再次考虑其他方法，只能用最原始的一招了，先在存储过程中组装SQL语句，然后再exec (@sql)一下，应该可以实现动态创建视图了。实践表明，这种方法是可行的，yeah，搞定！
        附上自己的代码，希望对有需要的兄弟有帮助。

/********************************************

功能：因为不确定HS6M_LOCATION对应的a表的具体编号，所以动态建立视图View_Inspection6Month，为Structure 6 Month 查询提供原始数据
逻辑：先判断是否存在视图View_Inspection6Month，如果存在则删除之；再从SDE_table_registry表中查询HS6M_LOCATION，得到a表的具体编号（比如109，etc.）；
然后根据逻辑组装SQL字符串；最后，执行这个字符串。

********************************************/

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 '+
      'sde.STRUCTURE_LOCATION ON '+
      'sde.HSI.STRUCTURE_ID = sde.STRUCTURE_LOCATION.STRUCTURE_ID LEFT OUTER JOIN '+
      'sde.INSPECTION ON sde.HSI.INSP_ID = sde.INSPECTION.INSP_ID LEFT OUTER JOIN '+
      '(select * from ' + @tablename + ' b where b.SDE_STATE_ID=(select max(a.SDE_STATE_ID) from ' + @tablename + ' a where b.OBJECTID=a.OBJECTID)) c ON '+
      ' sde.HSI.INSP_ID = c.INSP_ID LEFT OUTER JOIN '+
      'sde.INSP_EO ON sde.HSI.INSP_ID = sde.INSP_EO.INSP_ID '+
'WHERE (c.DELETED <> ' + '1' + ') AND (sde.HSI.DELETED <> ' + '1' + ') AND '+
      '(sde.INSPECTION.DELETED <> ' + '1' + ') '
/*******************
执行SQL语句变量，注意要加括号，否则不能运行，切记切记
********************/
exec (@sql)
end
GO

/********************************************
代码结束
********************************************/