Create A View With Dynamic Sql

Question

I'm trying to create a dynamic database creation script.

There are a lot of steps and we create this database often so the script looks something like this.

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

This is all well and good except for one view that we'd like to create in @databaseName.

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

1. My first thought was to simply set the database context and then create the view in one script. Unfortunately, this didn't work because CREATE VIEW must be the first statement in its query block (details).

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

2. To get around (1) I tried to set the context separately so that CREATE VIEW would be the first command in the EXEC. This did create the view but did so within my current context and not @databaseName. It seem that the effects of calling USE in EXEC only persist until the end of that EXEC statement (details).

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

3. Next I tried putting everything back into one script but included a GO command in order to make CREATE VIEW the first command in a new query block. This failed because GO isn't allowed within an EXEC script (details).

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

4. Finally I tried to specify the target database as part of the CREATE VIEW command. In this case the script failed because CREATE VIEW doesn't allow the database to be specified as part of its creation (details).

   --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]')
   

Any suggestions? I think this should be a common use case but Google wasn't able to help me.

Saccharify

Answer 1

You can do this by double nesting the dynamic SQL statements then:

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

Reactionary

Amazing. I love it. I'll test really fast. – Saccharify

WOW! Been bashing my head on a problem for 3 days and never thought of this approach! Brilliant, thanks! – Lloyd

Over 2 years later, and its still quite useful. Thank you! – Admission

Brilliant. 7+ years later and this is still the only [easy] way to achieve this. – Vibraphone

Answer 2

Instead of double nesting, another approach is to create a stored procedure whose only purpose is to executes dynamic SQL

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

The stored procedure above can be re-used. Anytime you need to create a view just call the stored procedure and pass it the dynamic sql.

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

I prefer this approach because dynamic sql is confusing enough and adding double nesting complicates it further.

Pyrene