خرید بک لینک

Vote count: 0

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

asked 31 secs ago

برچسب: dynamic sql generation for the updatecommand is not supported,dynamic sql must declare the scalar variable,dynamic sql must declare the table variable, نویسنده: استخدام کار تاريخ: پنجشنبه 4 شهريور 1395 ساعت: 8:54

صفحه بندی