Is it possible to execute a statement with multiple uses of “GO”?

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?

edited Nov 21 '18 at 13:38

a_horse_with_no_name

305k46466562

asked Nov 21 '18 at 13:27

Rowan Richards

120212

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

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 |

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?

edited Nov 21 '18 at 13:38

a_horse_with_no_name

305k46466562

asked Nov 21 '18 at 13:27

Rowan Richards

120212

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

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 |

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?

edited Nov 21 '18 at 13:38

a_horse_with_no_name

305k46466562

asked Nov 21 '18 at 13:27

Rowan 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

edited Nov 21 '18 at 13:38

a_horse_with_no_name

305k46466562

asked Nov 21 '18 at 13:27

Rowan Richards

120212

edited Nov 21 '18 at 13:38

a_horse_with_no_name

305k46466562

asked Nov 21 '18 at 13:27

Rowan 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

asked Nov 21 '18 at 13:27

Rowan Richards

120212

asked Nov 21 '18 at 13:27

Rowan Richards

120212

asked Nov 21 '18 at 13:27

Rowan Richards

120212

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

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 |

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

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

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 |

2 Answers
2

active

oldest

votes

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

sepupic

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 |

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 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');
});

Post as a guest

Name

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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

sepupic

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 |

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

sepupic

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 |

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

sepupic

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

sepupic

6,6731315

answered Nov 21 '18 at 13:37

sepupic

6,6731315

answered Nov 21 '18 at 13:37

sepupic

6,6731315

answered Nov 21 '18 at 13:37

sepupic

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

add a comment |

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 Haider

664516

add a comment |

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 Haider

664516

add a comment |

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 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 Haider

664516

answered Nov 21 '18 at 13:54

Hamza Haider

664516

answered Nov 21 '18 at 13:54

Hamza Haider

664516

answered Nov 21 '18 at 13:54

Hamza Haider

664516

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Agfdhyk