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.