Making scripts rerunnable

• mtnbikur

  SSC Enthusiast

  Points: 169

  More actions

  May 23, 2018 at 2:05 pm

  #412183

  I've always tried to make any script I write rerunnable whether it's modifying a stored proc, altering a table structure or whatever.  And I've always been successful, even when it comes to modifying a primary key or something.  Here's one I haven't run into before though.

  Setup:

  
IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  CREATE TABLE dbo.EmpExample
(
    EmpId INT,
    EmpName VARCHAR(25)
)

  CREATE TABLE dbo.BldgExample
(
    EmpId INT,
    BldgId INT,
    BadgeNum INT
)

  INSERT dbo.EmpExample
(
  EmpId,
  EmpName
)
SELECT 1, 'Kevin'
UNION SELECT 2, 'John'
UNION SELECT 3, 'Michael'

  INSERT dbo.BldgExample
(
    EmpId,
    BldgId,
    BadgeNum
)
SELECT 1, 47, 123
UNION SELECT 2, 47, 124
UNION SELECT 3, 47, 125
UNION SELECT 2, 55, 124

  So John works in two buildings, so I want to clean up how this data is stored.  Makes more sense to tie the BadgeNum to the actual employee and not the building he's entering.

  
IF NOT EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'EmpExample'
    AND c.name = 'BadgeNum')
BEGIN

      ALTER TABLE dbo.EmpExample
    ADD BadgeNum INT

  END;
GO

  IF EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'BldgExample'
    AND c.name = 'BadgeNum')
BEGIN
    UPDATE emp
    SET    BadgeNum = bldg.BadgeNum
    FROM dbo.EmpExample emp
    INNER JOIN dbo.BldgExample bldg
        ON emp.EmpId = bldg.EmpId

      ALTER TABLE dbo.BldgExample
    DROP COLUMN BadgeNum
END;
GO

  When I run this the first time, it works just great.  But if this is part of a large change set where things get tweaked and the whole thing gets rerun, I'd like the whole thing to be rerunnable.  It fails the 2nd time around with a "column doesn't exist" error.   I know it doesn't exist.   That's why I wrapped it in an "if exists".

  I know there are quicker ways to write the "if exists" portions.   And overlook the fact that the update will have two source rows for John from the BldgExample table.   I was trying to come up with a contrived scenario.

  Ideas?  Comments?   Rude remarks?

  Thanks!

  Keith

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  May 23, 2018 at 3:38 pm

  #1991715

  mtnbikur - Wednesday, May 23, 2018 2:05 PM

  I've always tried to make any script I write rerunnable whether it's modifying a stored proc, altering a table structure or whatever.  And I've always been successful, even when it comes to modifying a primary key or something.  Here's one I haven't run into before though.

  Setup:

  
IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  CREATE TABLE dbo.EmpExample
(
    EmpId INT,
    EmpName VARCHAR(25)
)

  CREATE TABLE dbo.BldgExample
(
    EmpId INT,
    BldgId INT,
    BadgeNum INT
)

  INSERT dbo.EmpExample
(
  EmpId,
  EmpName
)
SELECT 1, 'Kevin'
UNION SELECT 2, 'John'
UNION SELECT 3, 'Michael'

  INSERT dbo.BldgExample
(
    EmpId,
    BldgId,
    BadgeNum
)
SELECT 1, 47, 123
UNION SELECT 2, 47, 124
UNION SELECT 3, 47, 125
UNION SELECT 2, 55, 124

  So John works in two buildings, so I want to clean up how this data is stored.  Makes more sense to tie the BadgeNum to the actual employee and not the building he's entering.

  
IF NOT EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'EmpExample'
    AND c.name = 'BadgeNum')
BEGIN

      ALTER TABLE dbo.EmpExample
    ADD BadgeNum INT

  END;
GO

  IF EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'BldgExample'
    AND c.name = 'BadgeNum')
BEGIN
    UPDATE emp
    SET    BadgeNum = bldg.BadgeNum
    FROM dbo.EmpExample emp
    INNER JOIN dbo.BldgExample bldg
        ON emp.EmpId = bldg.EmpId

      ALTER TABLE dbo.BldgExample
    DROP COLUMN BadgeNum
END;
GO

  When I run this the first time, it works just great.  But if this is part of a large change set where things get tweaked and the whole thing gets rerun, I'd like the whole thing to be rerunnable.  It fails the 2nd time around with a "column doesn't exist" error.   I know it doesn't exist.   That's why I wrapped it in an "if exists".

  I know there are quicker ways to write the "if exists" portions.   And overlook the fact that the update will have two source rows for John from the BldgExample table.   I was trying to come up with a contrived scenario.

  Ideas?  Comments?   Rude remarks?

  Thanks!

  Keith

  You can't do the drop and create in the same batch. Try something like:

  IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  GO

  CREATE TABLE dbo.EmpExample......

  Sue

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 23, 2018 at 3:39 pm

  #1991716

  mtnbikur - Wednesday, May 23, 2018 2:05 PM

  I've always tried to make any script I write rerunnable whether it's modifying a stored proc, altering a table structure or whatever.  And I've always been successful, even when it comes to modifying a primary key or something.  Here's one I haven't run into before though.

  Setup:

  
IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  CREATE TABLE dbo.EmpExample
(
    EmpId INT,
    EmpName VARCHAR(25)
)

  CREATE TABLE dbo.BldgExample
(
    EmpId INT,
    BldgId INT,
    BadgeNum INT
)

  INSERT dbo.EmpExample
(
  EmpId,
  EmpName
)
SELECT 1, 'Kevin'
UNION SELECT 2, 'John'
UNION SELECT 3, 'Michael'

  INSERT dbo.BldgExample
(
    EmpId,
    BldgId,
    BadgeNum
)
SELECT 1, 47, 123
UNION SELECT 2, 47, 124
UNION SELECT 3, 47, 125
UNION SELECT 2, 55, 124

  So John works in two buildings, so I want to clean up how this data is stored.  Makes more sense to tie the BadgeNum to the actual employee and not the building he's entering.

  
IF NOT EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'EmpExample'
    AND c.name = 'BadgeNum')
BEGIN

      ALTER TABLE dbo.EmpExample
    ADD BadgeNum INT

  END;
GO

  IF EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'BldgExample'
    AND c.name = 'BadgeNum')
BEGIN
    UPDATE emp
    SET    BadgeNum = bldg.BadgeNum
    FROM dbo.EmpExample emp
    INNER JOIN dbo.BldgExample bldg
        ON emp.EmpId = bldg.EmpId

      ALTER TABLE dbo.BldgExample
    DROP COLUMN BadgeNum
END;
GO

  When I run this the first time, it works just great.  But if this is part of a large change set where things get tweaked and the whole thing gets rerun, I'd like the whole thing to be rerunnable.  It fails the 2nd time around with a "column doesn't exist" error.   I know it doesn't exist.   That's why I wrapped it in an "if exists".

  I know there are quicker ways to write the "if exists" portions.   And overlook the fact that the update will have two source rows for John from the BldgExample table.   I was trying to come up with a contrived scenario.

  Ideas?  Comments?   Rude remarks?

  Thanks!

  Keith

  Don't have time to test this, but if the DML statement using the column you've dropped were being called in dynamic SQL, then your IF EXISTS would actually work and the syntax checker wouldn't complain.  So this would give you idempotency I think.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• mtnbikur

  SSC Enthusiast

  Points: 169

  More actions

  May 23, 2018 at 4:04 pm

  #1991723

  Sue_H - Wednesday, May 23, 2018 3:38 PM

  mtnbikur - Wednesday, May 23, 2018 2:05 PM

  I've always tried to make any script I write rerunnable whether it's modifying a stored proc, altering a table structure or whatever.  And I've always been successful, even when it comes to modifying a primary key or something.  Here's one I haven't run into before though.

  Setup:

  
IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  CREATE TABLE dbo.EmpExample
(
    EmpId INT,
    EmpName VARCHAR(25)
)

  CREATE TABLE dbo.BldgExample
(
    EmpId INT,
    BldgId INT,
    BadgeNum INT
)

  INSERT dbo.EmpExample
(
  EmpId,
  EmpName
)
SELECT 1, 'Kevin'
UNION SELECT 2, 'John'
UNION SELECT 3, 'Michael'

  INSERT dbo.BldgExample
(
    EmpId,
    BldgId,
    BadgeNum
)
SELECT 1, 47, 123
UNION SELECT 2, 47, 124
UNION SELECT 3, 47, 125
UNION SELECT 2, 55, 124

  So John works in two buildings, so I want to clean up how this data is stored.  Makes more sense to tie the BadgeNum to the actual employee and not the building he's entering.

  
IF NOT EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'EmpExample'
    AND c.name = 'BadgeNum')
BEGIN

      ALTER TABLE dbo.EmpExample
    ADD BadgeNum INT

  END;
GO

  IF EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'BldgExample'
    AND c.name = 'BadgeNum')
BEGIN
    UPDATE emp
    SET    BadgeNum = bldg.BadgeNum
    FROM dbo.EmpExample emp
    INNER JOIN dbo.BldgExample bldg
        ON emp.EmpId = bldg.EmpId

      ALTER TABLE dbo.BldgExample
    DROP COLUMN BadgeNum
END;
GO

  When I run this the first time, it works just great.  But if this is part of a large change set where things get tweaked and the whole thing gets rerun, I'd like the whole thing to be rerunnable.  It fails the 2nd time around with a "column doesn't exist" error.   I know it doesn't exist.   That's why I wrapped it in an "if exists".

  I know there are quicker ways to write the "if exists" portions.   And overlook the fact that the update will have two source rows for John from the BldgExample table.   I was trying to come up with a contrived scenario.

  Ideas?  Comments?   Rude remarks?

  Thanks!

  Keith

  You can't do the drop and create in the same batch. Try something like:

  IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  GO

  CREATE TABLE dbo.EmpExample......

  Sue

  Thanks Sue.  My issue isn't with the drop/creation of the tables in this contrived example.  In my *real* code, the tables are permanent and in place.   The issue is the 2nd chunk of SQL that tries to use a column that's since been removed from the table even though the statement is wrapped in the "if exists".   I tried Matt's response and it works like a charm.   Thanks!

• mtnbikur

  SSC Enthusiast

  Points: 169

  More actions

  May 23, 2018 at 4:05 pm

  #1991724

  Matt Miller (4) - Wednesday, May 23, 2018 3:39 PM

  mtnbikur - Wednesday, May 23, 2018 2:05 PM

  I've always tried to make any script I write rerunnable whether it's modifying a stored proc, altering a table structure or whatever.  And I've always been successful, even when it comes to modifying a primary key or something.  Here's one I haven't run into before though.

  Setup:

  
IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  CREATE TABLE dbo.EmpExample
(
    EmpId INT,
    EmpName VARCHAR(25)
)

  CREATE TABLE dbo.BldgExample
(
    EmpId INT,
    BldgId INT,
    BadgeNum INT
)

  INSERT dbo.EmpExample
(
  EmpId,
  EmpName
)
SELECT 1, 'Kevin'
UNION SELECT 2, 'John'
UNION SELECT 3, 'Michael'

  INSERT dbo.BldgExample
(
    EmpId,
    BldgId,
    BadgeNum
)
SELECT 1, 47, 123
UNION SELECT 2, 47, 124
UNION SELECT 3, 47, 125
UNION SELECT 2, 55, 124

  So John works in two buildings, so I want to clean up how this data is stored.  Makes more sense to tie the BadgeNum to the actual employee and not the building he's entering.

  
IF NOT EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'EmpExample'
    AND c.name = 'BadgeNum')
BEGIN

      ALTER TABLE dbo.EmpExample
    ADD BadgeNum INT

  END;
GO

  IF EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'BldgExample'
    AND c.name = 'BadgeNum')
BEGIN
    UPDATE emp
    SET    BadgeNum = bldg.BadgeNum
    FROM dbo.EmpExample emp
    INNER JOIN dbo.BldgExample bldg
        ON emp.EmpId = bldg.EmpId

      ALTER TABLE dbo.BldgExample
    DROP COLUMN BadgeNum
END;
GO

  When I run this the first time, it works just great.  But if this is part of a large change set where things get tweaked and the whole thing gets rerun, I'd like the whole thing to be rerunnable.  It fails the 2nd time around with a "column doesn't exist" error.   I know it doesn't exist.   That's why I wrapped it in an "if exists".

  I know there are quicker ways to write the "if exists" portions.   And overlook the fact that the update will have two source rows for John from the BldgExample table.   I was trying to come up with a contrived scenario.

  Ideas?  Comments?   Rude remarks?

  Thanks!

  Keith

  Don't have time to test this, but if the DML statement using the column you've dropped were being called in dynamic SQL, then your IF EXISTS would actually work and the syntax checker wouldn't complain.  So this would give you idempotency I think.

  That worked great!   Thanks Matt!

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  May 23, 2018 at 4:19 pm

  #1991725

  mtnbikur - Wednesday, May 23, 2018 4:04 PM

  Sue_H - Wednesday, May 23, 2018 3:38 PM

  mtnbikur - Wednesday, May 23, 2018 2:05 PM

  I've always tried to make any script I write rerunnable whether it's modifying a stored proc, altering a table structure or whatever.  And I've always been successful, even when it comes to modifying a primary key or something.  Here's one I haven't run into before though.

  Setup:

  
IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  CREATE TABLE dbo.EmpExample
(
    EmpId INT,
    EmpName VARCHAR(25)
)

  CREATE TABLE dbo.BldgExample
(
    EmpId INT,
    BldgId INT,
    BadgeNum INT
)

  INSERT dbo.EmpExample
(
  EmpId,
  EmpName
)
SELECT 1, 'Kevin'
UNION SELECT 2, 'John'
UNION SELECT 3, 'Michael'

  INSERT dbo.BldgExample
(
    EmpId,
    BldgId,
    BadgeNum
)
SELECT 1, 47, 123
UNION SELECT 2, 47, 124
UNION SELECT 3, 47, 125
UNION SELECT 2, 55, 124

  So John works in two buildings, so I want to clean up how this data is stored.  Makes more sense to tie the BadgeNum to the actual employee and not the building he's entering.

  
IF NOT EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'EmpExample'
    AND c.name = 'BadgeNum')
BEGIN

      ALTER TABLE dbo.EmpExample
    ADD BadgeNum INT

  END;
GO

  IF EXISTS (
    SELECT TOP 1 1
    FROM sys.tables t
    INNER JOIN sys.columns c
        ON t.object_id = c.object_id
    WHERE t.name = 'BldgExample'
    AND c.name = 'BadgeNum')
BEGIN
    UPDATE emp
    SET    BadgeNum = bldg.BadgeNum
    FROM dbo.EmpExample emp
    INNER JOIN dbo.BldgExample bldg
        ON emp.EmpId = bldg.EmpId

      ALTER TABLE dbo.BldgExample
    DROP COLUMN BadgeNum
END;
GO

  When I run this the first time, it works just great.  But if this is part of a large change set where things get tweaked and the whole thing gets rerun, I'd like the whole thing to be rerunnable.  It fails the 2nd time around with a "column doesn't exist" error.   I know it doesn't exist.   That's why I wrapped it in an "if exists".

  I know there are quicker ways to write the "if exists" portions.   And overlook the fact that the update will have two source rows for John from the BldgExample table.   I was trying to come up with a contrived scenario.

  Ideas?  Comments?   Rude remarks?

  Thanks!

  Keith

  You can't do the drop and create in the same batch. Try something like:

  IF OBJECT_ID('dbo.EmpExample') IS NOT NULL DROP TABLE dbo.EmpExample
IF OBJECT_ID('dbo.BldgExample') IS NOT NULL DROP TABLE dbo.BldgExample

  GO

  CREATE TABLE dbo.EmpExample......

  Sue

  Thanks Sue.  My issue isn't with the drop/creation of the tables in this contrived example.  In my *real* code, the tables are permanent and in place.   The issue is the 2nd chunk of SQL that tries to use a column that's since been removed from the table even though the statement is wrapped in the "if exists".   I tried Matt's response and it works like a charm.   Thanks!

  Sorry about that - my bad. Didn't realize you weren't really doing the drops.

  Sue

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 23, 2018 at 4:49 pm

  #1991729

  Really weird part is that I run the code with no problems multiple times.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  May 23, 2018 at 5:24 pm

  #1991733

  That is odd...I got the errors just due to the tables not dropping in the batch. Once or twice it would run a few times in a row  but that would be normal with that issue.
  I was testing it on 2014. But I don't think versions would even matter.

  Sue

• mtnbikur

  SSC Enthusiast

  Points: 169

  More actions

  May 24, 2018 at 10:43 am

  #1991857

  Lynn Pettis - Wednesday, May 23, 2018 4:49 PM

  Really weird part is that I run the code with no problems multiple times.

  I had it run multiple times without error in certain environments too.  But it always eventually errored out.  I don't know if the pre-compile step hits a DMV that's not instantly up-to-date, so it runs correctly a few times.  But if you leave it in a state where the column is missing for a while and then come back and try to run it, that always generated the error for me.

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  May 24, 2018 at 11:00 am

  #1991868

  Lynn Pettis - Wednesday, May 23, 2018 4:49 PM

  Really weird part is that I run the code with no problems multiple times.

  it's version dependent.  When I run the OP's second script multiple times on a 2008 SQL Server, it fails as described, when I run the same exact script on a newer SQL Server it works.  They must have changed how the parser handles DML between versions.