Create View in specified database with dynamic sql?

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?

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)

StackExchange Database Administrators Q#12127, answer score: 25