Persist SQL Variables Across Multiple "Go" Statements

go.sql

/*
challenge: persist vars across GO statements
solution: store vars in a temp table
*/

CREATE TABLE #vars_to_remember
(
  varName VARCHAR(20) PRIMARY KEY,
  value BIT
)

INSERT INTO #vars_to_remember SELECT 'did_create_table',0
GO

-- create new_table if it doesn't exist
IF NOT EXISTS
(
  SELECT *
  FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[new_table]') AND type in (N'U')
)
  BEGIN
    -- CREATE TABLE STUFF GOES HERE
    UPDATE #vars_to_remember
    SET value = 1 where VarName = 'did_create_table'
  END
GO

IF EXISTS (SELECT value from #vars_to_remember where varName = 'did_create_table' and value = 1)
-- additional conditions can go here
  BEGIN
    -- code that you only want to execute if the table was created during the execution of this script
  END
 GO
 
 -- don't forget to tidy up
 DROP TABLE #vars_to_remember