SQL Server:动态SQL错误:"CREATE VIEW"必须是查询批处理中的第一条语句 [英] SQL Server : dymanic SQL error: 'CREATE VIEW' must be the first statement in a query batch

问题描述

我用动态SQL编写了一个存储过程，当我在参数时调用它时抛出错误:

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

错误:

第15层状态1的第81行，消息102
'GO'附近的语法不正确.
消息102，第15级，州1，第91行
'GO'附近的语法不正确.
消息111，第15级，状态1，第93行
"CREATE VIEW"必须是查询批处理中的第一条语句.
消息111，第15级，状态1，第97行
"CREATE VIEW"必须是查询批处理中的第一条语句.
消息111，第15级，状态1，第104行
"CREATE VIEW"必须是查询批处理中的第一条语句.
消息111，第15级，状态1，第110行
"CREATE VIEW"必须是查询批处理中的第一条语句.
消息102，第15级，状态1，第114行
'GO'附近的语法不正确.

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

在该过程中，我打印了@sql进行查看.我复制打印的代码并将其粘贴到另一个查询中，它可以正常工作.-所以我现在完全不知道如何调试它....

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

更新:

我将每个 CREATE VIEW 分成不同的字符串，然后分别执行.-使用 BEGIN END 包装每个 CREATE VIEW 不起作用.

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]

错误:

关键字"SELECT"附近的语法不正确.

Incorrect syntax near the keyword 'SELECT'.

当我分别运行 CREATE VIEW 和 SELECT 时，它可以工作.

It works when I run CREATE VIEW and SELECT separately.

好答案: VIEW 必须是批处理中唯一的语句-感谢@ZLK

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

推荐答案

关于 GO :

GO不是Transact-SQL语句；这是由sqlcmd和osql实用程序以及SQL Server Management Studio代码编辑器.

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

...

以此类推.

这篇关于SQL Server:动态SQL错误:"CREATE VIEW"必须是查询批处理中的第一条语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！