使用动态SQL创建视图 [英] 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*/')

除了我们要在 @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. 我的第一个想法是简单地设置数据库上下文，然后在一个脚本中创建视图.不幸的是，这没有用，因为 CREATE VIEW 必须是其查询块中的第一条语句(

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

要解决这一问题(1)，我尝试单独设置上下文，以使 CREATE VIEW 将成为 EXEC 中的第一个命令.这确实创建了视图，但是是在我当前的上下文中创建的，而不是 @databaseName .似乎在 EXEC 中调用 USE 的效果只持续到该 EXEC 语句的结尾(

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

接下来，我尝试将所有内容放回一个脚本中，但包括一个 GO 命令，以使 CREATE VIEW 成为新查询块中的第一个命令.失败是因为 EXEC 脚本中不允许使用 GO (详细信息).

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

最后，我尝试将目标数据库指定为 CREATE VIEW 命令的一部分.在这种情况下，脚本会失败，因为 CREATE VIEW 不允许在创建数据库时指定数据库(

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

有什么建议吗?我认为这应该是一个常见的用例，但是Google无法帮助我.

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

推荐答案

您可以通过双重嵌套动态SQL语句，然后执行以下操作:

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

这篇关于使用动态SQL创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！