Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

When to use "GO" in T-SQL

  1. Jan 27, 2016 #1
    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.

    Code (SQL):

    -- 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;
     
     
  2. jcsd
  3. Jan 27, 2016 #2

    jedishrfu

    Staff: Mentor

    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:

     
  4. Feb 13, 2016 #3

    harborsparrow

    User Avatar
    Gold Member

    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.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: When to use "GO" in T-SQL
  1. SQL basics (Replies: 4)

  2. SQL relationships (Replies: 3)

  3. Sql join? (Replies: 4)

  4. SQL Help (Replies: 2)

Loading...