Hi I wrote a stored procedure with dynamic sql. The dynamic sql throws 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
