视图中不能声明变量，不能调用存储过程，如果写比较复杂的查询，需要应用存储过程
视图也可以和函数结合
存储过程通过select或其他语句返回结果集
除此之外，存储过程返回结果只有两种方式 1 return，2 output参数（主要）
http://blog.csdn.net/liangweiwei130/article/details/6691207
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[GL_AllItemNamep]','p') ) --视图v ,存储过程p，表u
DROP proc[dbo].[GL_AllItemNamep]
GO
create proc gl_allitemnamep @tblname as nvarchar(200) --注意此处没有括号
as
declare @sql as nvarchar(2000)
set @sql='' --不赋值，在使用@sql=@sql+'.....' 不能初始化
SET @sql = '
if exists( select * from sysobjects where id=object_id('''+ @tblname + ''') and type=''u'')
drop table ' +@tblname
print @sql
EXEC(@sql)
set @sql='if not exists(select 1 from tempdb..sysobjects where name='''+@tblname +''' and xtype=''u'')
select ''97'' as citem_class,citemcode,citemname into '+@tblname +' from fitemss97 where 1=0 ' --应用where 1=0 建立数据表结构，再用insert into 插入数据，对于大数据量来说会比select into 效率高
exec(@sql)
print @sql
set @sql=''
declare @citem_class as nvarchar(10)
declare @ctable as nvarchar(100)
declare @cur as cursor
set @cur=CURSOR FOR( select citem_class ,ctable from fitemclass)
open @cur
fetch @cur into @citem_class,@ctable
while @@FETCH_STATUS =0
begin
print @sql
set @sql= @sql+ ' union select N''' + @citem_class + ''' as citem_class,citemcode,citemname from ' + @ctable + ' '
fetch next from @cur into @citem_class,@ctable
end
close @cur
deallocate @cur
set @sql= ' insert into ' +@tblname + ' select N''ch'' as citem_class,cInvCode as citemcode,cInvName as citemname from inventory ' + @sql
exec (@sql)
print @sql
go
执行
exec GL_AllItemNamep 'AllItemName'
select * from AllItemName