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

[baggio785]

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条件来创建视图,请大家帮忙看看!!

[simonhehe]

--调整楼上的语句

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)
END

exec(@sql)

[simonhehe]

-------------如果上面的语句调不通,你就按下边的方式就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

[simonhehe]

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

[baggio785]

谢谢二位～

[hellowork]

只能使用动态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)

[soft78]

学习