我用动态 SQL 编写了一个存储过程,当我在参数中调用它时会抛出错误:
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:
消息 102,第 15 级,状态 1,第 81 行
“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”附近的语法不正确。
行号是无用的,因为它们是我的代码末尾之后的行......
在程序中我打印了@sql来看看。我复制打印的代码并将其粘贴到另一个查询中,它起作用了。 - 所以我现在完全不知道如何调试它......
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
有人可以帮忙吗?任何帮助表示赞赏。谢谢
UPDATE:
我把每一个都分开CREATE VIEW
分成不同的字符串,并分别执行它们。
- 使用BEGIN
END
包裹每一个CREATE VIEW
不起作用。
现在这部分仍然抛出错误:
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:
关键字“SELECT”附近的语法不正确。
当我跑步时它起作用CREATE VIEW
and SELECT
分别地。
得到答案:VIEW
必须是批次中唯一的语句 - 感谢@ZLK