存储过程不能在视图中被调用。 如下,不过不推荐: create table tb(...) insert tb exec procName create view vTest as select * from tb go
用select 返回數據集即可,注意存储过程不能在视图中被调用。
--例子:create proc p_createview @idlist varchar(1000), --查询条件 @viewname sysname --视图名 as declare @sql varchar(8000) set @sql='if exists (select 1 from dbo.sysobjects where id = object_id(N'['+@viewname+']') and OBJECTPROPERTY(id, N'IsView') = 1) drop view ['+@viewname+']' exec(@sql) set @sql='create view ['+@viewname+'] as select * from 表 where id in('+@idlist+')' exec(@sql) go --调用方法 exec p_createview '1,2,3','test' select * from test
如下,不过不推荐:
create table tb(...)
insert tb exec procName
create view vTest
as
select * from tb
go
@idlist varchar(1000), --查询条件
@viewname sysname --视图名
as
declare @sql varchar(8000)
set @sql='if exists (select 1 from dbo.sysobjects where id = object_id(N'['+@viewname+']') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view ['+@viewname+']'
exec(@sql)
set @sql='create view ['+@viewname+']
as
select * from 表 where id in('+@idlist+')'
exec(@sql)
go
--调用方法
exec p_createview '1,2,3','test'
select * from test