How to break down and run a large T-SQL query without generating errors?

[SlurrerOfSpeech]

I have the following query which I have not yet run because I know it will generate a lot of errors due to me not having GO in appropriate places. But instead of running it and guess-and-checking where to put GO until it's right, I'd like to understand where to insert GO statements and why. From what I understand, it is a way of "refreshing" MS SQL Server to get rid of the previous context or something. Any help greatly appreciated and I will like your answer if it's particularly helpful.

-- Create database for all information
-- needed in the application, set as context
CREATE DATABASE SurveyDb;
USE SurveyDb;
-- Create surveys table
CREATE TABLE Surveys (
    id INT IDENTITY(1,1),
    title VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);
-- Create sprocs for adding and deleting surveys
CREATE PROCEDURE AddSurvey
    @title VARCHAR(100) NOT NULL
AS
    INSERT INTO Surveys (title) VALUES (@title)
CREATE PROCEDURE DeleteSurvey
    @id INT
AS
    DELETE FROM Surveys WHERE id=@id
-- Seed the surveys table with 1 sample survey
EXEC AddSurvey @title = "Survey Numero Uno";

-- Create partners table
CREATE TABLE Partners (
    id INT IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);
-- Create sprocs for adding and deleting partners
CREATE PROCEDURE AddParter
    @name VARCHAR(50) NOT NULL
AS
    INSERT INTO Partners (name) VALUES (@name)
CREATE PROCEDURE DeletePartner
    @id INT
AS
    DELETE FROM Partners WHERE id=@id
-- Seed the partners table with a few samples
EXEC AddPartner @name = "Haliburton";
EXEC AddPartner @name = "Berkshite Hathaway";
EXEC AddPartner @name = "Infosys";

-- Create questions table. Questions are associated with
-- a particular survey. If the survey is deleted then so
-- are all associated questions.
CREATE TABLE Questions (
    id INT IDENTITY(1,1) PRIMARY KEY,
    survery_id INT,
    text VARCHAR(300) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (survey_id) REFERENCES Surveys(id) ON DELETE CASCADE
);
-- Create sprocs for adding and deleting questions.
CREATE PROCEDURE AddQuestion
    @survey_id INT NOT NULL, 
    @text VARCHAR(300) NOT NULL
AS
    INSERT INTO Questions (survey_id, text) VALUES (@survey_id, @text)
CREATE PROCEDURE DeleteQuestion
    @id INT
AS
    DELETE FROM Questions WHERE id=@id
-- Seed the questions table with sample questions.
EXEC AddQuestion @survey_id = 1, @text = "What is the average velocity of an African swallow?";
EXEC AddQuestion @survey_id = 1, @text = "How hot is she, on a scale of 1.0-100.0?";

-- Create table for answers. Answers are associated with both
-- a question and a partner. If either the question or partner
-- is deleted then so are all associated answers.
CREATE TABLE Answers (
    id INT IDENTITY (1,1),
    question_id INT,
    partner_id INT,
    val DECIMAL NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (question_id) REFERENCES Questions(id) ON DELETE CASCADE,
    FOREIGN KEY (partner_id) REFERENCES Partners(id) ON DELETE CASCADE
);

-- Create sprocs for adding and deleting answers.
CREATE PROCEDURE AddAnswer
    @question_id INT NOT NULL, 
    @partner_id INT NOT NULL,
    @val DECIMAL NOT NULL
AS
    INSERT INTO Answers (question_id, partner_id, val) VALUES (@question_id, @partner_id, @val)
CREATE PROCEDURE DeleteAnswers
    @id INT
AS
    DELETE FROM Answers WHERE id=@id
-- Seed the questions table with sample answers.
EXEC AddAnswer @question_id = 1, @partner_id = 1, @val = 23.3;
EXEC AddAnswer @question_id = 2, @partner_id = 1, @val = 99.5;
EXEC AddAnswer @question_id = 1, @partner_id = 2, @val = 0.12345;
EXEC AddAnswer @question_id = 2, @partner_id = 2, @val = 0.19;

 

[jedishrfu]

Stackoverflow had a thread on this:

http://stackoverflow.com/questions/...you-use-go-and-when-should-you-use-semi-colon

which referenced this MS document:

https://technet.microsoft.com/en-us/library/aa172435(SQL.80).aspx

which said:

These rules apply to batches:

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must begin the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
• A table cannot be altered and then the new columns referenced in the same batch.
• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

 

[harborsparrow]

Just a comment on the content of the proc:

I would certainly not try running that huge thing all at once. I would break it down into several smaller pieces to run separately, then (eventually) maybe combine the parts after testing-- if combining is necessary.