使用動態 SQL 從游標創建視圖

我想迭代表中列出的表名並創建一個列出所有資訊的視圖。我有游標集，我有視圖集的基礎，但是如何修改語法以添加 UNION ALL 語句而不是重複 Create View 語句？

這是我的語法：

Create Table dbo.tabletoprocess
(
   id int IDENTITY(1,1) PRIMARY KEY NOT NULL 
   ,DatabaseName varchar(100) NOT NULL
   ,TablePrefix varchar(25) NOT NULL
   ,ActiveCustomer varchar(50) NOT NULL
)



Insert Into dbo.tabletoprocess (DatabaseName, TablePrefix, ActiveCustomer) VALUES
('arc', 'night91_', 'yes')
,('bell', 'abc123_', 'yes')
,('fed', 'fred13_', 'yes')
,('rea', 'bad12_', 'yes')
,('bva', 'red121_', 'yes')
,('sez', 'bc12_', 'yes')

Declare @dbname varchar(100), @tableprefix varchar(100), @sql nvarchar(max), @fullpre varchar(500)

Declare DB_CURSOR CURSOR FOR

   Select databasename, tableprefix
   FROM tabletoprocess 
   WHERE ActiveCustomer = 'yes'

   OPEN DB_CURSOR
   FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

   Set @fullpre = @dbname + '.' + @tableprefix

   WHILE @@FETCH_STATUS = 0
   BEGIN

       Set @sql = 'Create View dbo.Test As 
                   Select *                        
                       FROM OPENQUERY(linkedserver, ''Select op.field1, op.field2, o.field3, a.field4, 
                                               a.field5, a.field6, a.field7, a.field8, a.field9, 
                                               o.field10, o.field11 
                                               FROM '+@fullpre+'orderinfo as op 
                                               LEFT JOIN '+@fullpre+'order as o ON op.oi=o.oi 
                                               LEFT JOIN '+@fullpre+'address as a
                                               ON o.osa_id=a.ai'')';
       Print @sql

        FETCH NEXT FROM DB_CURSOR INTO @dbname, @tableprefix

   END

CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

您是否嘗試過set像這樣修改命令：

Set @sql = isnull(@sql,'') +
          case when @sql is NULL
               then ' create view dbo.Test as ' 
               else ' union all '
          end +
          'SELECT ...'

顯然 (?) print @sql(and exec @sql) 是在游標循環完成/關閉後發出的。

---

這是一個顯示結果的範例小提琴：

declare @sql varchar(max), @cr char(1)
set @cr = char(10)

Set @sql = isnull(@sql,'') + @cr +
          case when @sql is NULL
               then 'create view dbo.Test as ' 
               else 'union all '
          end + @cr +
          'SELECT x from mytable1 ...'

Set @sql = isnull(@sql,'') + @cr +
          case when @sql is NULL
               then 'create view dbo.Test as ' 
               else 'union all '
          end + @cr +
          'SELECT y from mytable2 ...'

Set @sql = isnull(@sql,'') + @cr +
          case when @sql is NULL
               then 'create view dbo.Test as ' 
               else 'union all '
          end + @cr +
          'SELECT z from mytable3 ...'

print @sql

以及上面執行的結果：

create view dbo.Test as 
SELECT x from mytable1 ...
union all 
SELECT y from mytable2 ...
union all 
SELECT z from mytable3 ...

引用自：https://dba.stackexchange.com/questions/183639