16

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?

King Chan
  • 399
  • 2
  • 4
  • 11

2 Answers2

25

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)
Martin Smith
  • 77,689
  • 15
  • 224
  • 316
  • 1
    +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 Feb 02 '12 at 16:44
  • 1
    @KingChan - you can both upvote and accept, FYI ;) – JNK Feb 02 '12 at 16:54
  • @JNK +1 of course~ :) – King Chan Feb 02 '12 at 17:42
  • 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 ! –  Feb 18 '13 at 01:49
  • You are a hero. Will name my firstborn child after you. – Jens May 03 '17 at 12:16
-1

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)
sgojgini
  • 21
  • 3