1

I have update and alter statements that are only running when they are put into they're own batch. Below is my shortened version. It'd probably be a good idea to skip the code and read my question below.

DROP TABLE #tmpImportData
USE database_name
GO

DECLARE @localVar VARCHAR(MAX) = 'data'
DECLARE @localVar VARCHAR(MAX) = 'data'
DECLARE @localVar VARCHAR(MAX) = 'data'
DECLARE @localVar int = 1
--Etc...

DECLARE @localVar INT
DECLARE @localVarVARCHAR(50)    
DECLARE @localVar AS BINARY (16)    
---Etc...


CREATE TABLE #tmpImportData (GuideFirstName VARCHAR(MAX), GuideLastName VARCHAR(MAX), 
                             email VARCHAR(MAX), group_id_text VARCHAR(MAX), CandidateName VARCHAR(MAX), 
                             grade_text VARCHAR(5), dateofbirth DATETIME

                             )

SET @sql = 'BULK INSERT #tmpImportData FROM '''+@fileName+''' WITH (FIELDTERMINATOR ='';'', ROWTERMINATOR = ''\n'', FIRSTROW = 1)'
EXEC (@sql)

--John O. How many rows from the import
SELECT @countCandidateImport = count(*) FROM #tmpImportData

--Fix some import data before any proccessing. We need to remove any punctuation, extra spaces, etc.
UPDATE #tmpImportData SET CandidateName = LTRIM(RTRIM(CandidateName)) FROM #tmpImportData
UPDATE #tmpImportData SET CandidateName = Replace(CandidateName,'.', '') FROM #tmpImportData
--UPDATE #tmpImportData SET CandidateName = Replace(CandidateName,',', '') FROM #tmpImportData

--Check DB for current district. If not exists create new one
SELECT @districtId = id, @ditrict_admins_restricted = CountAdminPermited, @candidates_restricted = count_candidate_restricted
FROM DistrictsTable WHERE name = RTRIM(LTRIM(@districtName))


--John O. What is whytry?
IF @districtId IS NULL
BEGIN
    INSERT INTO districtstable (name, active, CountAdminPermited, why_try_permitted) VALUES (RTRIM(LTRIM(@districtName)), 1, 3, @iswhyTry)
    SELECT @districtId = SCOPE_IDENTITY()
END
ELSE BEGIN
    SELECT @count_created_district_admins = count(*) 
    FROM GuidesTable gt
    INNER JOIN guides_to_permission gtp ON gtp.guide_id = gt.ID
    WHERE gt.DistrictID = @districtId AND gtp.permission_id = 0 AND gt.Active = 1

    IF(@count_created_district_admins >= @ditrict_admins_restricted)
    BEGIN
        SET @messageID = 1
    END

    SELECT @count_created_candidates = count(*)
    FROM CandidatesTable ct
    WHERE ct.DistrictID =  @districtId AND ct.Active = 1
    IF @count_created_candidates + @countCandidateImport >= @candidates_restricted
    BEGIN
        SET @messageID = 2  
    END

    IF(@messageID <> -1)
        RETURN
END
select 1
--Create School
DECLARE @schoolId AS INT
SET @schoolId = NULL
SELECT @schoolId = SchoolsTable.ID FROM SchoolsTable WHERE SchoolsTable.Name = LTRIM(RTRIM(@schoolName))
IF @schoolId IS NULL
    BEGIN
        INSERT INTO schoolstable (districtid, name, Active) VALUES (@districtId, @schoolName, 1)
        SELECT @schoolId = SCOPE_IDENTITY()
    END
ELSE 

SET @adminGuideId = NULL
SELECT @adminGuideId = GuidesTable.ID FROM GuidesTable WHERE LTRIM(RTRIM(FirstName)) = LTRIM(RTRIM(@FirstName)) 
    AND LTRIM(RTRIM(LastName)) = LTRIM(RTRIM(@LastName)) AND LTRIM(RTRIM(EmailAddress)) = LTRIM(RTRIM(@adminLogin))
select @adminGuideId
IF @adminGuideId IS NULL 
BEGIN
        SET @password = HASHBYTES('MD5', @passwordAdmin)                

        INSERT INTO GuidesTable (DistrictID, ClassID, FirstName         , LastName          , EmailAddress, [Password], createddate, Administrator, Active, website_access_level_id)
        VALUES                  (@districtId, 0     , @FirstName        , @LastName         , @adminLogin , @password , GetDate()  , 1            , 1     , 1)
        SET @tmpGuideId = SCOPE_IDENTITY()
        INSERT INTO guides_to_permission (guides_to_permission.guide_id ,guides_to_permission.permission_id) 
        VALUES                           (@tmpGuideId                   ,0                 )
        INSERT INTO guides_to_school_access (guides_to_school_access.guide_id , guides_to_school_access.school_id) 
        VALUES                              (@tmpGuideId                      , @schoolId                        )
END
ELSE BEGIN
        SELECT @AccessToSchoolId = gtsa.id FROM guides_to_school_access gtsa WHERE @adminGuideId = gtsa.guide_id AND @schoolId = gtsa.school_id
        IF @AccessToSchoolId IS NULL
        BEGIN
            INSERT INTO guides_to_school_access (guides_to_school_access.guide_id , guides_to_school_access.school_id) 
            VALUES                              (@adminGuideId                    , @schoolId                        )
        END
END
GO
--Create unique guides
--declare @SQL1 nvarchar(max)
--set @SQL1 = '
ALTER TABLE #tmpImportData ADD group_id INT
ALTER TABLE #tmpImportData ADD guide_id INT
ALTER TABLE #tmpImportData ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData ADD class_id INT 

--exec sp_executesql @SQL1      
GO

UPDATE #tmpImportData SET group_id = CAST(group_id_text AS INT)
UPDATE #tmpImportData SET group_id = 0 WHERE group_id IS NULL

SELECT * FROM #tmpImportData
GO

So the table updates and alters fine with the code above. But if I remove the two GO statements before the last one, and just leave the last one after the select. The table isn't updated or altered. Any idea why this is happening?

ALSO, IF I run this with NO GO statements everything is executed except the alter and the update and the select.

1
  • 2
    Just a brief side note: GO is not a (SQL) statement - it's a separator that is specific to SQL Server Mgmt Studio.
    – marc_s
    Commented Jan 20, 2015 at 22:02

2 Answers 2

4

I suspect that there's a database error that you're not seeing here.

If you remove the GO statement right before the two final UPDATE statements, I'd imagine that the query crashes with something like "unknown column 'group_id'".

This happens because an execution plan is generated for each batch at a time, i.e. every time you pass GO. In my experience, this plan does not always consider statements that make schema changes (except SELECT .. INTO operations and index operations). If you try to generate a query plan that includes the group_id column before the ALTER TABLE statements have actually been executed, that column won't actually exist yet.

My best advice may not work for every conceivable scenario, but it will for yours: create the temp table correctly from the start, in this case by including the columns that you're ADDing in the original CREATE TABLE statement at the top, so you won't have to ALTER it later on in the code.

3

GO (Transact-SQL)

Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

RETURN (Transact-SQL)

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

When you remove to GO your entire block of code is one batch. The RETURN you have terminates the batch and nothing more will be executed. When you have the GO in the code, execution will continue with the next batch when you do RETURN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.