GO causes error when used in EXEC: "Incorrect syntax near 'GO'."
Solution 1
1) EXEC[UTE]
can execute only T-SQL statements.
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. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.
2) You could replace
SET @SQL = '
--Drop Trigger
BEGIN TRY
DROP TRIGGER [dbo].[TR_' + @TableName + '_Audit]
END TRY
BEGIN CATCH
END CATCH
GO
--Create Trigger
CREATE TRIGGER [dbo].[TR_' + @TableName + '_Audit]
with
DECLARE @TriggerName SYSNAME;
SET @TriggerName = 'TR_' + @TableName + '_Audit';
IF EXISTS (
SELECT *
FROM sys.triggers
WHERE parent_id = OBJECT_ID(@TableName)
AND name = @TriggerName
)
BEGIN
SET @SQL = N'DROP TRIGGER [dbo].' + QUOTENAME(@TriggerName);
EXEC(@SQL);
END
SET @SQL = '
--Create Trigger
CREATE TRIGGER [dbo].[TR_' + @TableName + '_Audit]
or (better)
with
DECLARE @TriggerName SYSNAME;
SET @TriggerName = 'TR_' + @TableName + '_Audit';
IF NOT EXISTS (
SELECT *
FROM sys.triggers
WHERE parent_id = OBJECT_ID(@TableName)
AND name = @TriggerName
)
BEGIN
SET @SQL = N'CREATE TRIGGER [dbo].' + QUOTENAME(@TriggerName) + 'ON ' + @TableName + ' AFTER INSERT, UPDATE, DELETE AS BEGIN SELECT NULL END';
EXEC(@SQL);
END
SET @SQL = '
--Alter Trigger
ALTER TRIGGER [dbo].[TR_' + @TableName + '_Audit]
Note: The object's name should be NVARCHAR(128)
or SYSNAME
.
Solution 2
I got the same error, how i fixed this
create or alter procedure mypro
@msg varchar(20)
as
print(@msg)
go;
exec mypro @msg='fft';
output Incorrect syntax near 'go'
Started executing query at Line 1
Msg 102, Level 15, State 1, Procedure mypro, Line 6
Incorrect syntax near 'go'.
remove ;
after go it will work.
create or alter procedure mypro
@msg varchar(20)
as
print(@msg)
go;
exec mypro @msg='fft';
[12:17:59 pm] Started executing query at Line 1
Commands completed successfully.
[12:17:59 pm] Started executing query at Line 7
fft
Total execution time: 00:00:00.062
Solution 3
The error says what the problem is exactly. You are creating a dynamic sql statement to be run all at once by the EXEC
statement. GO
is a batch separator for use in a command line interface or SSMS. It separates multiple statements to be executed separately. Therefore, you cannot have GO
in query statements to be executed by EXEC
or sp_executesql
.
Simply remove the GO
statement, or create two queries to be run by two EXEC
statements.
Scott
Updated on June 19, 2022Comments
-
Scott about 2 years
I've created this stored procedure which dynamically creates the same trigger for all my tables:
USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --Drop Stored Procedure BEGIN TRY DROP PROCEDURE [dbo].[sp_CreateDataChangedTrigger] END TRY BEGIN CATCH END CATCH GO --Create Stored Procedure -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ -- ============================================= -- Author: Scott Bass -- Create date: 06JUL2014 -- Description: Create Data Change triggers -- ============================================= CREATE PROCEDURE sp_CreateDataChangedTrigger -- Add the parameters for the stored procedure here @TableName varchar(255), @TableKey varchar(255), @Debug bit=1 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @SQL varchar(max); SET @SQL = ' --Drop Trigger BEGIN TRY DROP TRIGGER [dbo].[TR_' + @TableName + '_Audit] END TRY BEGIN CATCH END CATCH GO --Create Trigger CREATE TRIGGER [dbo].[TR_' + @TableName + '_Audit] ON [dbo].[' + @TableName + '] AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @event_type [char] --Get Event Type IF EXISTS(SELECT * FROM INSERTED) IF EXISTS(SELECT * FROM DELETED) SELECT @event_type = ''U'' ELSE SELECT @event_type = ''I'' ELSE IF EXISTS(SELECT * FROM deleted) SELECT @event_type = ''D'' ELSE --no rows affected - cannot determine event SELECT @event_type = ''K'' IF @event_type IN (''I'',''U'') BEGIN DECLARE @CurrentUserID INT; SELECT @CurrentUserID = u.UserID FROM [dbo].[dim_Users] u WHERE u.[Username] = dbo.udfUserName() UPDATE t SET DateModified = GETDATE(), WhoModifiedID = @CurrentUserID FROM INSERTED e JOIN [dbo].[' + @TableName + '] t ON e.[' + @TableKey + '] = t.[' + @TableKey + '] END IF @event_type = ''D'' BEGIN no_op: --Nothing for now END END GO '; IF @Debug=1 BEGIN set nocount on; print @SQL; END ELSE BEGIN exec(@SQL); END END GO
If I call the SP with the debug option:
SET NOCOUNT ON; DECLARE @return_value int EXEC @return_value = [dbo].[sp_CreateDataChangedTrigger] @TableName = N'dim_Status', @TableKey = N'StatusID', @Debug = 1 SELECT 'Return Value' = @return_value GO
Then submit the results from the Messages window, it works fine.
But, when I turn the @Debug switch off, I get these error messages:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 13
'CREATE TRIGGER' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near 'GO'.Thanks...