SQL Server : dymanic SQL error: 'CREATE VIEW' must be the first statement in a query batch

Question

I wrote a stored procedure with dynamic SQL which throws an error when I call it when arguments:

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

Error:

Msg 102, Level 15, State 1, Line 81
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 91
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 93
'CREATE VIEW' must be the first statement in a query batch.
Msg 111, Level 15, State 1, Line 97
'CREATE VIEW' must be the first statement in a query batch.
Msg 111, Level 15, State 1, Line 104
'CREATE VIEW' must be the first statement in a query batch.
Msg 111, Level 15, State 1, Line 110
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 114
Incorrect syntax near 'GO'.

The line number is useless, since they are lines after the end of my code...

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....

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

Can somebody help please? Any help's appreciated. Thanks

UPDATE:

I split each CREATE VIEW into different strings, and EXEC them separately. - Use BEGIN END to wrap each CREATE VIEW doesn't work.

Now still this piece throws error:

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]

Error:

Incorrect syntax near the keyword 'SELECT'.

It works when I run CREATE VIEW and SELECT separately.

Got answer: VIEW has to be the only statement in a batch - thanks to @ZLK

Answer 1

About GO:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Split your query into separate requests:

EXEC sp_executesql N'
USE CTR

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

EXEC sp_executesql N'
CREATE VIEW [dbo].[two_columns] AS
SELECT [hash_vcc] ,[legal_name] FROM [dbo].[ctr_vendor_pay]'

...

And so on.