Create A View With Dynamic Sql

问题:

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 we create in this database.

The problem as I understand it stems from three rules in SQL regarding the EXEC command

1. USE contexts are only alive for the life of the EXEC
2. 'CREATE VIEW' must be the first statement in a query batch
3. GO isn't actually a SQL Command and as such isn't allowed in dynamic sql
4. On CREATE VIEW you can only specify down to Schema.

So here are three things I've tried without success.

--1.Results in my view not being created in my database
EXEC ('USE [' + @databaseName + ']')
EXEC ('CREATE VIEW')

--2.Results in a 'CREATE VIEW' must be the first statement in a query batch
EXEC 
('
    USE [' + @databaseName + ']
    CREATE VIEW
')

--3.Results in Incorrect syntax near 'GO'
EXEC 
('
    USE [' + @databaseName + ']
    GO
    CREATE VIEW
')

--4.Results in '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.

回答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

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