存储过程中建立动态视图

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

转载于:https://www.cnblogs.com/Ly426/p/4309367.html