我需要根据一些输入参数在存储过程中新建一个View,请问我该如何写?
CREATE procedure dbo.proc
@type smallint
as
if Exists ...
drop view ...
GO
create view ...
as select...
这样为什么不行?
---------------------------------------------------------------
create view ...语句需要放在第一条,或者用EXEC动态执行。
---------------------------------------------------------------
一般这种情况都要用EXEC动态执行,下面的代码我已测试过,你可以自己做相应的修改:
CREATE procedure createview
@type char(5)
as
declare @sqlstr varchar(255)
if exists (select * from sysobjects where id = object_id(N'[dbo].[VIEW1]') and OBJECTPROPERTY(id, N'IsView') = 1)
begin
select @sqlstr = 'drop view VIEW1'
exec(@sqlstr)
end
begin
select @sqlstr = 'create view VIEW1 as select * from A where 主键 = "' + @type + '"'
exec(@sqlstr)
end
---------------------------------------------------------------
@SQL = 'CREATE VIEW AAA()'
EXEC (@SQL)
在过程里面执行SQL语句要用EXEC ()