Is it possible to execute a statement with multiple uses of “GO”?
1
I am trying to execute a batch (as part of a larger script) on MS SQL Server and it is returning a list of errors:
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'ALTER'.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near 'GO'.
etc
This is the batch:
IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ANS_247LIB_CIRCDESK_CONFIG_PROFILE')
BEGIN
DECLARE @SqlStr nvarchar(max)
SET @SqlStr= '
CREATE TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE](
[CONFIG_ID] [int] IDENTITY(1,1) NOT NULL,
[SEARCH_BY_NAME] [nchar](1) NOT NULL,
[SEARCH_BY_BARCODE] [nchar](1) NOT NULL,
[SEARCH_BY_DOB] [nchar](1) NOT NULL,
[DISPLAY_USERID] [nchar](1) NOT NULL,
[DISPLAY_CHARGES] [nchar](1) NOT NULL,
[DISPLAY_RESERVATIONS] [nchar](1) NOT NULL,
[DISPLAY_DOB] [nchar](1) NOT NULL,
[DISPLAY_STATUS] [nchar](1) NOT NULL,
[DISPLAY_GENDER] [nchar](1) NOT NULL,
[DISPLAY_REFS] [nchar](1) NOT NULL,
[DISPLAY_LIBRARY] [nchar](1) NOT NULL,
[DISPLAY_EMAIL] [nchar](1) NOT NULL,
[DISPLAY_MOBILENO] [nchar](1) NOT NULL,
[FOREGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BACKGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BUTTON_COLOR] [nvarchar](10) NOT NULL,
[BOX_COLOR] [nvarchar](10) NOT NULL,
[TEXT_COLOR] [nvarchar](10) NOT NULL,
[PROFILE_ID] [int] NOT NULL,
CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_pk] PRIMARY KEY CLUSTERED
(
[CONFIG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__292D09F3] DEFAULT (''Y'') FOR [SEARCH_BY_NAME]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2A212E2C] DEFAULT (''Y'') FOR [SEARCH_BY_BARCODE]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2B155265] DEFAULT (''Y'') FOR [SEARCH_BY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2C09769E] DEFAULT (''Y'') FOR [DISPLAY_USERID]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__541767F8] DEFAULT (''Y'') FOR [DISPLAY_CHARGES]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2CFD9AD7] DEFAULT (''Y'') FOR [DISPLAY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2DF1BF10] DEFAULT (''Y'') FOR [DISPLAY_STATUS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2EE5E349] DEFAULT (''Y'') FOR [DISPLAY_GENDER]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2FDA0782] DEFAULT (''Y'') FOR [DISPLAY_REFS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__30CE2BBB] DEFAULT (''Y'') FOR [DISPLAY_LIBRARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__31C24FF4] DEFAULT (''Y'') FOR [DISPLAY_EMAIL]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__32B6742D] DEFAULT (''Y'') FOR [DISPLAY_MOBILENO]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__FOREG__33AA9866] DEFAULT (''#E0F7F7'') FOR [FOREGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__BACKG__349EBC9F] DEFAULT (''#00BCD4'') FOR [BACKGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BUTTON_COLOR] DEFAULT (''#00BCD4'') FOR [BUTTON_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BOX_COLOR] DEFAULT (''#00BCD4'') FOR [BOX_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_TEXT_COLOR] DEFAULT (''#FFFFFF'') FOR [TEXT_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] WITH CHECK ADD CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk] FOREIGN KEY([PROFILE_ID])
REFERENCES [dbo].[ANS_PROFILE] ([PROFILE_ID])
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] CHECK CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk]
GO
'
EXEC (@SqlStr)
END
GO
I know I can execute these statements one at a time, but it is preferred that this script is executed all at once, if possible.
Can you have multiple uses of "GO" in a string when using EXEC?
sql-server tsql ssms edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
add a comment |
1
I am trying to execute a batch (as part of a larger script) on MS SQL Server and it is returning a list of errors:
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'ALTER'.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near 'GO'.
etc
This is the batch:
IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ANS_247LIB_CIRCDESK_CONFIG_PROFILE')
BEGIN
DECLARE @SqlStr nvarchar(max)
SET @SqlStr= '
CREATE TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE](
[CONFIG_ID] [int] IDENTITY(1,1) NOT NULL,
[SEARCH_BY_NAME] [nchar](1) NOT NULL,
[SEARCH_BY_BARCODE] [nchar](1) NOT NULL,
[SEARCH_BY_DOB] [nchar](1) NOT NULL,
[DISPLAY_USERID] [nchar](1) NOT NULL,
[DISPLAY_CHARGES] [nchar](1) NOT NULL,
[DISPLAY_RESERVATIONS] [nchar](1) NOT NULL,
[DISPLAY_DOB] [nchar](1) NOT NULL,
[DISPLAY_STATUS] [nchar](1) NOT NULL,
[DISPLAY_GENDER] [nchar](1) NOT NULL,
[DISPLAY_REFS] [nchar](1) NOT NULL,
[DISPLAY_LIBRARY] [nchar](1) NOT NULL,
[DISPLAY_EMAIL] [nchar](1) NOT NULL,
[DISPLAY_MOBILENO] [nchar](1) NOT NULL,
[FOREGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BACKGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BUTTON_COLOR] [nvarchar](10) NOT NULL,
[BOX_COLOR] [nvarchar](10) NOT NULL,
[TEXT_COLOR] [nvarchar](10) NOT NULL,
[PROFILE_ID] [int] NOT NULL,
CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_pk] PRIMARY KEY CLUSTERED
(
[CONFIG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__292D09F3] DEFAULT (''Y'') FOR [SEARCH_BY_NAME]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2A212E2C] DEFAULT (''Y'') FOR [SEARCH_BY_BARCODE]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2B155265] DEFAULT (''Y'') FOR [SEARCH_BY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2C09769E] DEFAULT (''Y'') FOR [DISPLAY_USERID]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__541767F8] DEFAULT (''Y'') FOR [DISPLAY_CHARGES]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2CFD9AD7] DEFAULT (''Y'') FOR [DISPLAY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2DF1BF10] DEFAULT (''Y'') FOR [DISPLAY_STATUS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2EE5E349] DEFAULT (''Y'') FOR [DISPLAY_GENDER]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2FDA0782] DEFAULT (''Y'') FOR [DISPLAY_REFS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__30CE2BBB] DEFAULT (''Y'') FOR [DISPLAY_LIBRARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__31C24FF4] DEFAULT (''Y'') FOR [DISPLAY_EMAIL]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__32B6742D] DEFAULT (''Y'') FOR [DISPLAY_MOBILENO]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__FOREG__33AA9866] DEFAULT (''#E0F7F7'') FOR [FOREGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__BACKG__349EBC9F] DEFAULT (''#00BCD4'') FOR [BACKGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BUTTON_COLOR] DEFAULT (''#00BCD4'') FOR [BUTTON_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BOX_COLOR] DEFAULT (''#00BCD4'') FOR [BOX_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_TEXT_COLOR] DEFAULT (''#FFFFFF'') FOR [TEXT_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] WITH CHECK ADD CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk] FOREIGN KEY([PROFILE_ID])
REFERENCES [dbo].[ANS_PROFILE] ([PROFILE_ID])
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] CHECK CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk]
GO
'
EXEC (@SqlStr)
END
GO
I know I can execute these statements one at a time, but it is preferred that this script is executed all at once, if possible.
Can you have multiple uses of "GO" in a string when using EXEC?
sql-server tsql ssms edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
GOis an SSMS (andsqlcmd) keyword, not T-SQL, which acts as a batch separator. You can't useGOin dynamic SQL as it isn't a valid T-SQL term. If you want to execute dynamic SQL in different batches you have to make multiple calls tosp_executesql.
– Larnu
Nov 21 '18 at 13:37
1
Why do you think that EXEC()ing this is any different to just running the set of queries inside the string?
– Caius Jard
Nov 21 '18 at 13:37
;is a statement terminator, not a batch separator. It won't have the same effect @LittleSweetSeas.
– Larnu
Nov 21 '18 at 13:43
add a comment |
1
1
1
I am trying to execute a batch (as part of a larger script) on MS SQL Server and it is returning a list of errors:
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'ALTER'.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near 'GO'.
etc
This is the batch:
IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ANS_247LIB_CIRCDESK_CONFIG_PROFILE')
BEGIN
DECLARE @SqlStr nvarchar(max)
SET @SqlStr= '
CREATE TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE](
[CONFIG_ID] [int] IDENTITY(1,1) NOT NULL,
[SEARCH_BY_NAME] [nchar](1) NOT NULL,
[SEARCH_BY_BARCODE] [nchar](1) NOT NULL,
[SEARCH_BY_DOB] [nchar](1) NOT NULL,
[DISPLAY_USERID] [nchar](1) NOT NULL,
[DISPLAY_CHARGES] [nchar](1) NOT NULL,
[DISPLAY_RESERVATIONS] [nchar](1) NOT NULL,
[DISPLAY_DOB] [nchar](1) NOT NULL,
[DISPLAY_STATUS] [nchar](1) NOT NULL,
[DISPLAY_GENDER] [nchar](1) NOT NULL,
[DISPLAY_REFS] [nchar](1) NOT NULL,
[DISPLAY_LIBRARY] [nchar](1) NOT NULL,
[DISPLAY_EMAIL] [nchar](1) NOT NULL,
[DISPLAY_MOBILENO] [nchar](1) NOT NULL,
[FOREGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BACKGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BUTTON_COLOR] [nvarchar](10) NOT NULL,
[BOX_COLOR] [nvarchar](10) NOT NULL,
[TEXT_COLOR] [nvarchar](10) NOT NULL,
[PROFILE_ID] [int] NOT NULL,
CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_pk] PRIMARY KEY CLUSTERED
(
[CONFIG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__292D09F3] DEFAULT (''Y'') FOR [SEARCH_BY_NAME]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2A212E2C] DEFAULT (''Y'') FOR [SEARCH_BY_BARCODE]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2B155265] DEFAULT (''Y'') FOR [SEARCH_BY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2C09769E] DEFAULT (''Y'') FOR [DISPLAY_USERID]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__541767F8] DEFAULT (''Y'') FOR [DISPLAY_CHARGES]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2CFD9AD7] DEFAULT (''Y'') FOR [DISPLAY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2DF1BF10] DEFAULT (''Y'') FOR [DISPLAY_STATUS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2EE5E349] DEFAULT (''Y'') FOR [DISPLAY_GENDER]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2FDA0782] DEFAULT (''Y'') FOR [DISPLAY_REFS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__30CE2BBB] DEFAULT (''Y'') FOR [DISPLAY_LIBRARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__31C24FF4] DEFAULT (''Y'') FOR [DISPLAY_EMAIL]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__32B6742D] DEFAULT (''Y'') FOR [DISPLAY_MOBILENO]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__FOREG__33AA9866] DEFAULT (''#E0F7F7'') FOR [FOREGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__BACKG__349EBC9F] DEFAULT (''#00BCD4'') FOR [BACKGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BUTTON_COLOR] DEFAULT (''#00BCD4'') FOR [BUTTON_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BOX_COLOR] DEFAULT (''#00BCD4'') FOR [BOX_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_TEXT_COLOR] DEFAULT (''#FFFFFF'') FOR [TEXT_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] WITH CHECK ADD CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk] FOREIGN KEY([PROFILE_ID])
REFERENCES [dbo].[ANS_PROFILE] ([PROFILE_ID])
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] CHECK CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk]
GO
'
EXEC (@SqlStr)
END
GO
I know I can execute these statements one at a time, but it is preferred that this script is executed all at once, if possible.
Can you have multiple uses of "GO" in a string when using EXEC?
sql-server tsql ssms edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
I am trying to execute a batch (as part of a larger script) on MS SQL Server and it is returning a list of errors:
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'ALTER'.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near 'GO'.
etc
This is the batch:
IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ANS_247LIB_CIRCDESK_CONFIG_PROFILE')
BEGIN
DECLARE @SqlStr nvarchar(max)
SET @SqlStr= '
CREATE TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE](
[CONFIG_ID] [int] IDENTITY(1,1) NOT NULL,
[SEARCH_BY_NAME] [nchar](1) NOT NULL,
[SEARCH_BY_BARCODE] [nchar](1) NOT NULL,
[SEARCH_BY_DOB] [nchar](1) NOT NULL,
[DISPLAY_USERID] [nchar](1) NOT NULL,
[DISPLAY_CHARGES] [nchar](1) NOT NULL,
[DISPLAY_RESERVATIONS] [nchar](1) NOT NULL,
[DISPLAY_DOB] [nchar](1) NOT NULL,
[DISPLAY_STATUS] [nchar](1) NOT NULL,
[DISPLAY_GENDER] [nchar](1) NOT NULL,
[DISPLAY_REFS] [nchar](1) NOT NULL,
[DISPLAY_LIBRARY] [nchar](1) NOT NULL,
[DISPLAY_EMAIL] [nchar](1) NOT NULL,
[DISPLAY_MOBILENO] [nchar](1) NOT NULL,
[FOREGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BACKGROUND_COLOUR] [nvarchar](10) NOT NULL,
[BUTTON_COLOR] [nvarchar](10) NOT NULL,
[BOX_COLOR] [nvarchar](10) NOT NULL,
[TEXT_COLOR] [nvarchar](10) NOT NULL,
[PROFILE_ID] [int] NOT NULL,
CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_pk] PRIMARY KEY CLUSTERED
(
[CONFIG_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__292D09F3] DEFAULT (''Y'') FOR [SEARCH_BY_NAME]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2A212E2C] DEFAULT (''Y'') FOR [SEARCH_BY_BARCODE]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__SEARC__2B155265] DEFAULT (''Y'') FOR [SEARCH_BY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2C09769E] DEFAULT (''Y'') FOR [DISPLAY_USERID]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__541767F8] DEFAULT (''Y'') FOR [DISPLAY_CHARGES]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2CFD9AD7] DEFAULT (''Y'') FOR [DISPLAY_DOB]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2DF1BF10] DEFAULT (''Y'') FOR [DISPLAY_STATUS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2EE5E349] DEFAULT (''Y'') FOR [DISPLAY_GENDER]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__2FDA0782] DEFAULT (''Y'') FOR [DISPLAY_REFS]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__30CE2BBB] DEFAULT (''Y'') FOR [DISPLAY_LIBRARY]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__31C24FF4] DEFAULT (''Y'') FOR [DISPLAY_EMAIL]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__DISPL__32B6742D] DEFAULT (''Y'') FOR [DISPLAY_MOBILENO]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__FOREG__33AA9866] DEFAULT (''#E0F7F7'') FOR [FOREGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF__ANS_247LI__BACKG__349EBC9F] DEFAULT (''#00BCD4'') FOR [BACKGROUND_COLOUR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BUTTON_COLOR] DEFAULT (''#00BCD4'') FOR [BUTTON_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_BOX_COLOR] DEFAULT (''#00BCD4'') FOR [BOX_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] ADD CONSTRAINT [DF_ANS_247LIB_CIRCDESK_CONFIG_PROFILE_TEXT_COLOR] DEFAULT (''#FFFFFF'') FOR [TEXT_COLOR]
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] WITH CHECK ADD CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk] FOREIGN KEY([PROFILE_ID])
REFERENCES [dbo].[ANS_PROFILE] ([PROFILE_ID])
GO
ALTER TABLE [dbo].[ANS_247LIB_CIRCDESK_CONFIG_PROFILE] CHECK CONSTRAINT [ANS_247LIB_CIRCDESK_CONFIG_PROFILE_ANS_PROFILE_PROFILE_ID_fk]
GO
'
EXEC (@SqlStr)
END
GO
I know I can execute these statements one at a time, but it is preferred that this script is executed all at once, if possible.
Can you have multiple uses of "GO" in a string when using EXEC?
sql-server tsql ssms sql-server tsql ssms edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
edited Nov 21 '18 at 13:38
a_horse_with_no_name
305k46466562
305k46466562
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
asked Nov 21 '18 at 13:27
Rowan RichardsRowan Richards
120212
120212
GOis an SSMS (andsqlcmd) keyword, not T-SQL, which acts as a batch separator. You can't useGOin dynamic SQL as it isn't a valid T-SQL term. If you want to execute dynamic SQL in different batches you have to make multiple calls tosp_executesql.
– Larnu
Nov 21 '18 at 13:37
1
Why do you think that EXEC()ing this is any different to just running the set of queries inside the string?
– Caius Jard
Nov 21 '18 at 13:37
;is a statement terminator, not a batch separator. It won't have the same effect @LittleSweetSeas.
– Larnu
Nov 21 '18 at 13:43
add a comment |
GOis an SSMS (andsqlcmd) keyword, not T-SQL, which acts as a batch separator. You can't useGOin dynamic SQL as it isn't a valid T-SQL term. If you want to execute dynamic SQL in different batches you have to make multiple calls tosp_executesql.
– Larnu
Nov 21 '18 at 13:37
1
Why do you think that EXEC()ing this is any different to just running the set of queries inside the string?
– Caius Jard
Nov 21 '18 at 13:37
;is a statement terminator, not a batch separator. It won't have the same effect @LittleSweetSeas.
– Larnu
Nov 21 '18 at 13:43
GO is an SSMS (and sqlcmd) keyword, not T-SQL, which acts as a batch separator. You can't use GO in dynamic SQL as it isn't a valid T-SQL term. If you want to execute dynamic SQL in different batches you have to make multiple calls to sp_executesql.– Larnu
Nov 21 '18 at 13:37
GO is an SSMS (and sqlcmd) keyword, not T-SQL, which acts as a batch separator. You can't use GO in dynamic SQL as it isn't a valid T-SQL term. If you want to execute dynamic SQL in different batches you have to make multiple calls to sp_executesql.– Larnu
Nov 21 '18 at 13:37
1
1
Why do you think that EXEC()ing this is any different to just running the set of queries inside the string?
– Caius Jard
Nov 21 '18 at 13:37
Why do you think that EXEC()ing this is any different to just running the set of queries inside the string?
– Caius Jard
Nov 21 '18 at 13:37
; is a statement terminator, not a batch separator. It won't have the same effect @LittleSweetSeas.– Larnu
Nov 21 '18 at 13:43
; is a statement terminator, not a batch separator. It won't have the same effect @LittleSweetSeas.– Larnu
Nov 21 '18 at 13:43
add a comment |
2 Answers
2
active
oldest
votes
1
You CAN execute more than one statement in one EXEC command, but you cannot use GO (batch separator) within EXEC because it's not part of T-SQL:
SQL Server provides commands that are not Transact-SQL statements, but
are recognized by the sqlcmd and osql utilities and SQL Server
Management Studio Code Editor. These commands can be used to
facilitate the readability and execution of batches and scripts.
GO signals the end of a batch of Transact-SQL statements to the SQL
Server utilities.
SQL Server Utilities Statements - GO
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
add a comment |
2
In short Go is a batch separator.
- Batch is a group of one or multiple Transact-SQL statements sent at
the same time from an application to SQL Server for the execution of
the query and GO is a command which is used to tell the system that
is the end of a batch. It is not a T-SQL statement. - You need to use it only when its needed. So you need to keep in mind
that along with defining a batch using the GO command, you define
the scope of that piece of T-SQL code.
You will have to remove instances of GO in your dynamic SQL
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53413098%2fis-it-possible-to-execute-a-statement-with-multiple-uses-of-go%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You CAN execute more than one statement in one EXEC command, but you cannot use GO (batch separator) within EXEC because it's not part of T-SQL:
SQL Server provides commands that are not Transact-SQL statements, but
are recognized by the sqlcmd and osql utilities and SQL Server
Management Studio Code Editor. These commands can be used to
facilitate the readability and execution of batches and scripts.
GO signals the end of a batch of Transact-SQL statements to the SQL
Server utilities.
SQL Server Utilities Statements - GO
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
add a comment |
1
You CAN execute more than one statement in one EXEC command, but you cannot use GO (batch separator) within EXEC because it's not part of T-SQL:
SQL Server provides commands that are not Transact-SQL statements, but
are recognized by the sqlcmd and osql utilities and SQL Server
Management Studio Code Editor. These commands can be used to
facilitate the readability and execution of batches and scripts.
GO signals the end of a batch of Transact-SQL statements to the SQL
Server utilities.
SQL Server Utilities Statements - GO
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
add a comment |
1
1
1
You CAN execute more than one statement in one EXEC command, but you cannot use GO (batch separator) within EXEC because it's not part of T-SQL:
SQL Server provides commands that are not Transact-SQL statements, but
are recognized by the sqlcmd and osql utilities and SQL Server
Management Studio Code Editor. These commands can be used to
facilitate the readability and execution of batches and scripts.
GO signals the end of a batch of Transact-SQL statements to the SQL
Server utilities.
SQL Server Utilities Statements - GO
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
You CAN execute more than one statement in one EXEC command, but you cannot use GO (batch separator) within EXEC because it's not part of T-SQL:
SQL Server provides commands that are not Transact-SQL statements, but
are recognized by the sqlcmd and osql utilities and SQL Server
Management Studio Code Editor. These commands can be used to
facilitate the readability and execution of batches and scripts.
GO signals the end of a batch of Transact-SQL statements to the SQL
Server utilities.
SQL Server Utilities Statements - GO
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
answered Nov 21 '18 at 13:37
sepupicsepupic
6,6731315
6,6731315
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
add a comment |
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
I had completely forgotten you can use ";" to end a statement. I replaced the GOs with a ";" and all works as expected. Thank you!
– Rowan Richards
Nov 22 '18 at 15:24
add a comment |
2
In short Go is a batch separator.
- Batch is a group of one or multiple Transact-SQL statements sent at
the same time from an application to SQL Server for the execution of
the query and GO is a command which is used to tell the system that
is the end of a batch. It is not a T-SQL statement. - You need to use it only when its needed. So you need to keep in mind
that along with defining a batch using the GO command, you define
the scope of that piece of T-SQL code.
You will have to remove instances of GO in your dynamic SQL
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
add a comment |
2
In short Go is a batch separator.
- Batch is a group of one or multiple Transact-SQL statements sent at
the same time from an application to SQL Server for the execution of
the query and GO is a command which is used to tell the system that
is the end of a batch. It is not a T-SQL statement. - You need to use it only when its needed. So you need to keep in mind
that along with defining a batch using the GO command, you define
the scope of that piece of T-SQL code.
You will have to remove instances of GO in your dynamic SQL
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
add a comment |
2
2
2
In short Go is a batch separator.
- Batch is a group of one or multiple Transact-SQL statements sent at
the same time from an application to SQL Server for the execution of
the query and GO is a command which is used to tell the system that
is the end of a batch. It is not a T-SQL statement. - You need to use it only when its needed. So you need to keep in mind
that along with defining a batch using the GO command, you define
the scope of that piece of T-SQL code.
You will have to remove instances of GO in your dynamic SQL
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
In short Go is a batch separator.
- Batch is a group of one or multiple Transact-SQL statements sent at
the same time from an application to SQL Server for the execution of
the query and GO is a command which is used to tell the system that
is the end of a batch. It is not a T-SQL statement. - You need to use it only when its needed. So you need to keep in mind
that along with defining a batch using the GO command, you define
the scope of that piece of T-SQL code.
You will have to remove instances of GO in your dynamic SQL
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
answered Nov 21 '18 at 13:54
Hamza HaiderHamza Haider
664516
664516
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53413098%2fis-it-possible-to-execute-a-statement-with-multiple-uses-of-go%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
GOis an SSMS (andsqlcmd) keyword, not T-SQL, which acts as a batch separator. You can't useGOin dynamic SQL as it isn't a valid T-SQL term. If you want to execute dynamic SQL in different batches you have to make multiple calls tosp_executesql.– Larnu
Nov 21 '18 at 13:37
1
Why do you think that EXEC()ing this is any different to just running the set of queries inside the string?
– Caius Jard
Nov 21 '18 at 13:37
;is a statement terminator, not a batch separator. It won't have the same effect @LittleSweetSeas.– Larnu
Nov 21 '18 at 13:43