Create View with Dynamic Sql

Question

I am trying to create a dynamic database creation script.

There are many steps, and we often create this database to make the script look something like this.

 DECLARE @databaseName nvarchar(100) = 'DatabaseName'
 EXEC('/*A lot of database creation code built off of @databaseName*/')

This is all good, with the exception of one view that we would like to create in @databaseName.

I tried four different ways to create this view without success:

1. My first thought was to simply set up the database context and then create the view in one scenario. Unfortunately, this did not work, because it CREATE VIEWshould be the first statement in its query block ( more ).

   --Result: Error message, "'CREATE VIEW' must be the first statement in a query batch"
   EXEC 
   ('
       USE [' + @databaseName + ']
       CREATE VIEW
   ')
   

2. (1), , CREATE VIEW EXEC. , , @databaseName. , USE EXEC EXEC ().

   --Result: The view is created in the currently active database rather than @databaseName
   EXEC ('USE [' + @databaseName + ']')
   EXEC ('CREATE VIEW')
   

3. , GO, CREATE VIEW . , GO EXEC ().

   --Result: Error message, "Incorrect syntax near 'GO'"
   EXEC 
   ('
       USE [' + @databaseName + ']
       GO
       CREATE VIEW
   ')
   

4. , CREATE VIEW. , CREATE VIEW ().

   --Result: Error message, "'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name"
   EXEC ('CREATE VIEW [' + @databaseName + '].[dbo].[ViewName]')
   

- ? , , Google .

Answer 1

SQL:

begin tran
declare @sql nvarchar(max) = 
    N'use [AdventureWorks2012]; 
      exec (''create view Test as select * from sys.databases'')';

exec (@sql);

select * from AdventureWorks2012.sys.views
where name = 'Test'

rollback tran

Answer 2

, SQL

CREATE PROCEDURE [dbo].[util_CreateViewWithDynamicSQL] 
@sql nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    EXECUTE (@sql)  
END

. , , sql.

EXECUTE util_CreateViewWithDynamicSQL 'create view Test as select * from sys.databases'

, sql , .