using batch seperator 'GO' in dynamic sql query

Question

Here is the code below;

SET @QUERY = '

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- 
CREATE PROCEDURE [dbo].[sp_sp_duration_create_sp_duration_data_log]
@sp_name varchar(100),
@duration int,
@details varchar(250)
AS
BEGIN

IF @duration > 100
Begin
INSERT INTO [sp_duration_logs]
           ([sp_name]
           ,[duration]
           ,[details])
     VALUES
           (@sp_name
           ,@duration
           ,@details)
END
END

';
EXECUTE('USE ['+@DBNAME+']; '+@QUERY);

When I try to execute this t-sql command , I get an error:

Msg 111, Level 15, State 1, Procedure sp_sp_duration_create_sp_duration_data_log, Line 16 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I think I need to use 'GO' in query , but when I use GO another error occurring. (Invalid object named 'GO' or something else).

You can't use `GO` inside a dynamic SQL statement, as `GO` is not a valid T-SQL command see: https://msdn.microsoft.com/en-us/library/ms188037.aspx *`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.`*. — Mahmoud Gamal, Oct 12 '15 at 11:04

score 0 · Answer 1 · answered Oct 12 '15 at 10:30

0

SET @QUERY = 'SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO

//your code ';

EXECUTE('USE ['+@DBNAME+'] GO '+@QUERY);

answered Oct 12 '15 at 10:30

Nguyễn Hải Triều

1,454
1
8
14

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'GO'. – mtahtaci Oct 12 '15 at 10:47
i try that but i get an error again – mtahtaci Oct 12 '15 at 10:48

score 0 · Answer 2 · answered Oct 12 '15 at 10:54

Try this

SET @QUERY = '

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_sp_duration_create_sp_duration_data_log]
@sp_name varchar(100),
@duration int,
@details varchar(250)
AS
BEGIN

        IF @duration > 100
        Begin
        INSERT INTO [sp_duration_logs]
                   ([sp_name]
                   ,[duration]
                   ,[details])
             VALUES
                   (@sp_name
                   ,@duration
                   ,@details)
        END
END ';
EXECUTE('USE ['+@DBNAME+']; '+@QUERY);

using batch seperator 'GO' in dynamic sql query

2 Answers2