存储过程中建立动态视图
2015-03-02 17:35 马尔代夫_珍 阅读(258) 评论(0) 编辑 收藏 举报ALTER PROCEDURE CREATE_1_VIEW
AS BEGIN
IF exists(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[View_PF]') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW dbo.View_2
DECLARE test_cursor CURSOR SCROLL FOR
SELECT 3 from 4
OPEN test_cursor
DECLARE @PFDBNo nvarchar(11)
DECLARE @SQL VARCHAR(MAX)
declare @i int
set @i=1
set @SQL='create view [dbo].[View_2] as '
FETCH NEXT FROM test_cursor INTO @5
WHILE @@FETCH_STATUS=0
BEGIN
if @i=1
begin
set @SQL =@SQL + N'SELECT * FROM '+@5+'4 ' end else set @SQL =@SQL + ' union all ' + N'SELECT * FROM '+@5+'.4'
set @i=@i+1
FETCH NEXT FROM test_cursor INTO @5
END
CLOSE test_cursor
DEALLOCATE test_cursor
exec ( @sql)
END GO
