Use Dynamic SQL To Create View From Cursor

I want to iterate the table names that are listed in a table and create one view that has all of the info listed. I have the cursor set, I have the base of the view set, but how would I modify the syntax to add a UNION ALL statement instead of repeating the Create View statement?

This is the syntax I have:

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

Have you tried modifying the set command like such:

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

Obviously (?) the print @sql (and exec @sql) is issued after the cursor loop has been completed/closed.

Here’s a sample fiddle showing the results:

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

And the results of running the above:

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