title: SQL Server : dymanic SQL error: 'CREATE VIEW' must be the first statement in a query batch
body: <p>I wrote a stored procedure with dynamic SQL which throws an error when I call it when arguments:</p>
  
  <pre><code>IF OBJECT_ID('[dbo].[find_most_frequent]') IS NOT NULL
    DROP PROCEDURE [dbo].[find_most_frequent]
  GO
  
  CREATE PROCEDURE [dbo].[find_most_frequent] 
          @table_in VARCHAR(100),
          @table_out VARCHAR(100),
          @col_group VARCHAR(100),
          @col_2 VARCHAR(100) 
  AS
  BEGIN 
      DECLARE @sql NVARCHAR(4000);
  
      SET @sql =
      --start of code
      'USE CTR
  GO
  
  IF OBJECT_ID(N''[dbo].[two_columns]'', N''V'') IS NOT NULL
      DROP VIEW [dbo].[two_columns];
  
  IF OBJECT_ID(N''[dbo].[count_in_group]'', N''V'') IS NOT NULL
      DROP VIEW [dbo].[count_in_group];
  
  IF OBJECT_ID(N''[dbo].[rank_in_group]'', N''V'') IS NOT NULL
      DROP VIEW [dbo].[rank_in_group];
  
  IF OBJECT_ID(N''[dbo].[most_frequent_in_group]'', N''V'') IS NOT NULL
      DROP VIEW [dbo].[most_frequent_in_group];
  GO
  
  CREATE VIEW [dbo].[two_columns] AS
  SELECT ' +
          @col_group +
          ' ,' + @col_2 +
      ' FROM ' + @table_in +
  '
  GO
  
  CREATE VIEW [dbo].[count_in_group] AS
  SELECT  DISTINCT
          *
          ,COUNT(*) OVER(PARTITION BY ' + @col_group + ', ' + @col_2 + ') AS freq
      FROM [dbo].[two_columns]
  GO  
  
  CREATE VIEW [dbo].[rank_in_group] AS
  SELECT  *
          ,ROW_NUMBER() OVER (PARTITION BY ' + @col_group + ' ORDER BY freq DESC) AS rank_in_group
      FROM [dbo].[count_in_group]
  GO
  
  CREATE VIEW [dbo].[most_frequent_in_group] AS
  SELECT *
      FROM [dbo].[rank_in_group]
      WHERE rank_in_group = 1
  GO
  
  SELECT *
      INTO ' + @table_out + 
      ' FROM [dbo].[most_frequent_in_group]
  GO'
  --end of code    
      print @sql
      EXEC SP_EXECUTESQL @sql
  END 
  GO
  
  --call it
  EXEC [dbo].[find_most_frequent]
          @table_in = '[dbo].[table_1]'
          ,@table_out = '[dbo].[table_out]'
          ,@col_group = '[col_A]'
          ,@col_2 = '[col_B]' 
  GO
  </code></pre>
  
  <p>Error:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 81<br>
    Incorrect syntax near 'GO'.<br>
    Msg 102, Level 15, State 1, Line 91<br>
    Incorrect syntax near 'GO'.<br>
    Msg 111, Level 15, State 1, Line 93<br>
    'CREATE VIEW' must be the first statement in a query batch.<br>
    Msg 111, Level 15, State 1, Line 97<br>
    'CREATE VIEW' must be the first statement in a query batch.<br>
    Msg 111, Level 15, State 1, Line 104<br>
    'CREATE VIEW' must be the first statement in a query batch.<br>
    Msg 111, Level 15, State 1, Line 110<br>
    'CREATE VIEW' must be the first statement in a query batch.<br>
    Msg 102, Level 15, State 1, Line 114<br>
    Incorrect syntax near 'GO'.</p>
  </blockquote>
  
  <p>The line number is useless, since they are lines after the end of my code...</p>
  
  <p>In the procedure I printed @sql to take a look. I copy the printed code and paste it into another query, it worked. - So I totally don't know how to debug it now.... </p>
  
  <pre><code>USE CTR
  GO
  
  IF OBJECT_ID(N'[dbo].[two_columns]', N'V') IS NOT NULL
      DROP VIEW [dbo].[two_columns];
  
  IF OBJECT_ID(N'[dbo].[count_in_group]', N'V') IS NOT NULL
      DROP VIEW [dbo].[count_in_group];
  
  IF OBJECT_ID(N'[dbo].[rank_in_group]', N'V') IS NOT NULL
      DROP VIEW [dbo].[rank_in_group];
  
  IF OBJECT_ID(N'[dbo].[most_frequent_in_group]', N'V') IS NOT NULL
      DROP VIEW [dbo].[most_frequent_in_group];
  GO
  
  CREATE VIEW [dbo].[two_columns] AS
  SELECT [hash_vcc] ,[legal_name] FROM [dbo].[ctr_vendor_pay]
  GO
  
  CREATE VIEW [dbo].[count_in_group] AS
  SELECT  DISTINCT
          *
          ,COUNT(*) OVER(PARTITION BY [hash_vcc], [legal_name]) AS freq
      FROM [dbo].[two_columns]
  GO  
  
  CREATE VIEW [dbo].[rank_in_group] AS
  SELECT  *
          ,ROW_NUMBER() OVER (PARTITION BY [hash_vcc] ORDER BY freq DESC) AS rank_in_group
      FROM [dbo].[count_in_group]
  GO
  
  CREATE VIEW [dbo].[most_frequent_in_group] AS
  SELECT *
      FROM [dbo].[rank_in_group]
      WHERE rank_in_group = 1
  GO
  
  SELECT *
      INTO [dbo].[hashvcc_2_legalname] FROM [dbo].[most_frequent_in_group]
  GO
  </code></pre>
  
  <p>Can somebody help please? Any help's appreciated. Thanks</p>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>I split each <code>CREATE VIEW</code> into different strings, and EXEC them separately.
  - Use <code>BEGIN</code> <code>END</code> to wrap each <code>CREATE VIEW</code> doesn't work.</p>
  
  <p>Now still this piece throws error:</p>
  
  <pre><code>CREATE VIEW [dbo].[most_frequent_in_group] AS
  SELECT *
      FROM [dbo].[rank_in_group]
      WHERE rank_in_group = 1
  
  SELECT *
      INTO [dbo].[hashvcc_2_legalname]
      FROM [dbo].[most_frequent_in_group]
  </code></pre>
  
  <p>Error:</p>
  
  <blockquote>
    <p>Incorrect syntax near the keyword 'SELECT'.</p>
  </blockquote>
  
  <p>It works when I run <code>CREATE VIEW</code> and <code>SELECT</code> separately.</p>
  
  <p>Got answer: <code>VIEW</code> has to be the only statement in a batch - thanks to @ZLK</p>