SQL Server 2008 R2 – Use Dynamic SQL to Create View from Cursor

dynamic-sqlsql serversql-server-2008-r2t-sql

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

Best Answer

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 ...

Related Solutions

Sql-server – Create View in specified database with dynamic sql

You can use nested EXEC calls. The database context changed by the USE persists to the child batch.

DECLARE @DB SYSNAME

SET @DB = 'tempdb'

DECLARE @CreateViewStatement NVARCHAR(MAX) 
SET @CreateViewStatement = '
      USE '+ QUOTENAME(@DB) +';
      EXEC(''
             CREATE VIEW [dbo].[MyTable] AS
             SELECT 1 AS [Foo]
      '')

                          '
EXEC (@CreateViewStatement)

Sql-server – Create view in a different database with dynamic SQL

This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
  @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
  SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.

Another way that I've picked up since this original question appeared:

DECLARE 
  @DB  NVARCHAR(255) = QUOTENAME('dbname'),
  @SQL NVARCHAR(MAX),
  @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
  AS
    SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

This is slightly tidier because you don't have to double-nest single quotes.

And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += NCHAR(13) + NCHAR(10)    
  + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
  + '.sys.views WHERE name = ''vNonhiddenCategories'')
  AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''categories'')
  BEGIN
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
    CREATE VIEW dbo.vNonhiddenCategories3
    AS
      SELECT x = 1, y = ''''x'''';''
  END'
  FROM sys.databases 
  WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course "tidier" is in the eye of the beholder.

Best Answer

Related Solutions

Sql-server – Create View in specified database with dynamic sql

Sql-server – Create view in a different database with dynamic SQL

Related Question