CODEWITHSUNDEEP
sql-servert-sqlssms
Rowan Richards
Rowan Richards

Reputation: 399

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?

Upvotes: 1

Views: 319

Answers (2)

Hamza Haider
Hamza Haider

Reputation: 738

In short Go is a batch separator.

  1. 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.
  2. 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

Upvotes: 2

sepupic
sepupic

Reputation: 8687

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

Upvotes: 1

Related Questions