Create View in specified database with dynamic sql?

Question

I am writting a dynamic sql to drop and create view in different database.

So I wrote:

set @CreateViewStatement = 
 '
 USE ['+ @DB +'];
 CREATE VIEW [dbo].[MyTable]
 AS
 SELECT ........something
exec (@CreateViewStatement)

It gives me error:

'CREATE VIEW' must be the first statement in a query batch.

If I remove the USE DATABASE statement it works fine, but then the database is not specify anymore....

How can I solve this problem?

Answer 1

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)

• JNK: +1 - If you script out views using SMO this is how the framework does it as well - definitions are executed in dynamic SQL to get around the restriction
• JNK: @KingChan - you can both upvote and accept, FYI ;)
• King Chan: @JNK +1 of course~ :)
• user20268: definitely worked!! although i used it, wih many variables inside the nested query so i was a headache becuz of the quote handling! great solution though !
• Jens: You are a hero. Will name my firstborn child after you.

Answer 2

One way I have handled when run into this case is placing GO after use statement.

set @CreateViewStatement = 
'
 USE ['+ @DB +']; GO
 CREATE VIEW [dbo].[MyTable]
 AS
 SELECT ........something'
exec (@CreateViewStatement)

• King Chan: Just so you know, GO statment won't runs in exec
• Martin Smith: This won't work in the context of dynamic SQL. GO is a batch delimiter in the client tools not a TSQL keyword.