存储过程中创建视图的问题!!

Question

set @sql = 'if not exists (select 1
            from  sysobjects
           where  id = object_id(''dbo.V_'+@tablename_detail+''')
            and   type = ''V'')CREATE VIEW dbo.V_'+@tablename_detail+'
AS
SELECT dbo.'+@tablename_detail+'.downmain_id, 
      dbo.'+@tablename_detail+'.downmain_companyid, 
      dbo.'+@tablename_detail+'.downmain_songerid, 
      dbo.'+@tablename_detail+'.downmain_specialid, 
      dbo.'+@tablename_detail+'.downmain_songid, 
      dbo.'+@tablename_detail+'.downmain_browse, 
      dbo.'+@tablename_detail+'.downmain_download, 
      dbo.'+@tablename_detail+'.downmain_income, 
      dbo.'+@tablename_detail+'.downmain_date, 
      dbo.'+@tablename_detail+'.download_source, dbo.c_company.companyName, 
      dbo.music_song.songname, dbo.music_songer.songerName, 
      dbo.music_special.specialname, dbo.music_song.addtime
FROM dbo.music_special INNER JOIN
      dbo.music_songer INNER JOIN
      dbo.stat_downmain_200604 INNER JOIN
      dbo.music_song ON 
      dbo.'+@tablename_detail+'.downmain_songid = dbo.music_song.id ON 
      dbo.music_songer.id = dbo.'+@tablename_detail+'.downmain_songerid ON 
      dbo.music_special.id = dbo.'+@tablename_detail+'.downmain_specialid INNER JOIN
      dbo.c_company ON 
      dbo.'+@tablename_detail+'.downmain_companyid = dbo.c_company.id'
exec(@sql)错误提示为view关键字附近有语法错误,查了一下资料,说创建视图必须在一个事务内,因此create前必须有go关键字,如果按照一般的sql语句来创建没问题,现在我是根据if条件来创建视图,请大家帮忙看看!!

Answer 1

只能使用动态SQL语句了.在该语句中,当条件符合时,动态生成一个CREATE VIEW字符串并EXEC执行.
declare @sql varchar(3000)
declare @tablename_detail varchar(50)
set @tablename_detail = 'tmp'
set @sql = 'if not exists (select 1
            from  sysobjects
           where  id = object_id(''dbo.V_'+@tablename_detail+''')
            and   type = ''V'')
BEGIN
declare @defview varchar(1000)
set defview = ''
CREATE VIEW dbo.V_'+@tablename_detail+'
AS
SELECT dbo.'+@tablename_detail+'.downmain_id, 
      dbo.'+@tablename_detail+'.downmain_companyid, 
      dbo.'+@tablename_detail+'.downmain_songerid, 
      dbo.'+@tablename_detail+'.downmain_specialid, 
      dbo.'+@tablename_detail+'.downmain_songid, 
      dbo.'+@tablename_detail+'.downmain_browse, 
      dbo.'+@tablename_detail+'.downmain_download, 
      dbo.'+@tablename_detail+'.downmain_income, 
      dbo.'+@tablename_detail+'.downmain_date, 
      dbo.'+@tablename_detail+'.download_source, dbo.c_company.companyName, 
      dbo.music_song.songname, dbo.music_songer.songerName, 
      dbo.music_special.specialname, dbo.music_song.addtime
FROM dbo.music_special INNER JOIN
      dbo.music_songer INNER JOIN
      dbo.stat_downmain_200604 INNER JOIN
      dbo.music_song ON 
      dbo.'+@tablename_detail+'.downmain_songid = dbo.music_song.id ON 
      dbo.music_songer.id = dbo.'+@tablename_detail+'.downmain_songerid ON 
      dbo.music_special.id = dbo.'+@tablename_detail+'.downmain_specialid INNER JOIN
      dbo.c_company ON 
      dbo.'+@tablename_detail+'.downmain_companyid = dbo.c_company.id' + '''' + '
EXEC(@defview)
END'
--print @sql
exec(@sql)

Answer 2

--调整楼上的语句declare @sql varchar(3000)
declare @tablename_detail varchar(50)
set @tablename_detail = 'tmp'
set @sql = 'if not exists (select 1 from  sysobjects where id = object_id(''dbo.V_'+@tablename_detail+''')and type = ''V'')'
--print @sql
BEGIN
declare @defview varchar(1000)
set @defview = ''
select @defview='
CREATE VIEW dbo.V_'+@tablename_detail+'
AS
SELECT dbo.'+@tablename_detail+'.downmain_id, 
      dbo.'+@tablename_detail+'.downmain_companyid, 
      dbo.'+@tablename_detail+'.downmain_songerid, 
      dbo.'+@tablename_detail+'.downmain_specialid, 
      dbo.'+@tablename_detail+'.downmain_songid, 
      dbo.'+@tablename_detail+'.downmain_browse, 
      dbo.'+@tablename_detail+'.downmain_download, 
      dbo.'+@tablename_detail+'.downmain_income, 
      dbo.'+@tablename_detail+'.downmain_date, 
      dbo.'+@tablename_detail+'.download_source, dbo.c_company.companyName, 
      dbo.music_song.songname, dbo.music_songer.songerName, 
      dbo.music_special.specialname, dbo.music_song.addtime
FROM dbo.music_special INNER JOIN
      dbo.music_songer INNER JOIN
      dbo.stat_downmain_200604 INNER JOIN
      dbo.music_song ON 
      dbo.'+@tablename_detail+'.downmain_songid = dbo.music_song.id ON 
      dbo.music_songer.id = dbo.'+@tablename_detail+'.downmain_songerid ON 
      dbo.music_special.id = dbo.'+@tablename_detail+'.downmain_specialid INNER JOIN
      dbo.c_company ON 
      dbo.'+@tablename_detail+'.downmain_companyid = dbo.c_company.id'
--print @defview
EXEC(@defview)
ENDexec(@sql)

Answer 3

-------------如果上面的语句调不通,你就按下边的方式就OK
--建立存储过程
create proc create_view
@tablename varchar(100),
@view varchar(100)
as
begin tran
if not exists(select name from sysobjects where name = @tablename and type = 'v')
exec('create view dbo.'+@view+' as
select * from '+@tablename
)
commit tran
go--建立测试数据
CREATE TABLE [t1] ([id] [int] IDENTITY (1, 1) NOT NULL ,[col] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into t1 select 23--执行
exec dbo.create_view t1,viewt1

Answer 4

不好意思,应该将if not exists(select name from sysobjects where name = @tablename and type = 'v')中的name = @tablename 改成 name = @view