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

  • Thread starter SlurrerOfSpeech
  • Start date
In summary, the conversation discusses the use of GO statements in MS SQL Server and the advantages of using them in creating and managing databases. The conversation also references a Stackoverflow thread and a Microsoft document on the rules and best practices for using GO statements in batches of SQL code. The suggestion is made to break down large procedures into smaller parts for easier management and testing.
  • #1
SlurrerOfSpeech
141
11
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.

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;
 
Technology news on Phys.org
  • #2
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.
 
  • Like
Likes harborsparrow and SlurrerOfSpeech
  • #3
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.
 

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

1. When should I use "GO" in T-SQL?

The "GO" command in T-SQL is used to separate batches of code. It signals the end of one batch and the beginning of the next. This is useful when you have multiple statements or transactions that need to be executed separately.

2. Can I use "GO" in the middle of a transaction?

No, the "GO" command cannot be used in the middle of a transaction. It can only be used to separate batches of code, not within a single batch.

3. Does the use of "GO" affect the performance of my code?

No, using "GO" does not have any impact on the performance of your code. It is simply a way to structure your code and does not affect how it is executed.

4. How many times can I use "GO" in my code?

You can use "GO" as many times as needed in your code. However, it is important to note that each time you use "GO", it creates a new batch and any variables or temporary tables declared in the previous batch will no longer be available in the next batch.

5. Can I use "GO" in stored procedures or functions?

Yes, you can use "GO" in stored procedures or functions. Just like in regular T-SQL code, it will separate the batches of code within the stored procedure or function.

Similar threads

  • Programming and Computer Science
Replies
7
Views
870
  • Programming and Computer Science
Replies
1
Views
2K
  • Programming and Computer Science
Replies
1
Views
3K
  • Set Theory, Logic, Probability, Statistics
Replies
8
Views
745
  • Programming and Computer Science
Replies
1
Views
3K
Back
Top