CODEWITHSUNDEEP
sql-servert-sql
wingyip
wingyip

Reputation: 3536

Conditionally execute update query

Am preparing an update script for an old database that may potentially be run multiple times.

If it is run multiple times some db objects like columns may have disappeared so there will be some scripts that should not run depending on certain conditions.

For example the following. I only want to run the updates if the evtype column is still available. However, including this in a second run comes up with invalid column evtype.

How can I avoid this issue

PRINT 'Update event type IDs'
--IF COL_LENGTH('events', 'evtype') IS NOT NULL
IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'evtype'
          AND Object_ID = Object_ID(N'events'))
BEGIN
    UPDATE [events]
    SET evtypeid=1 WHERE evtype='d'
    UPDATE [events]
    SET evtypeid=2 WHERE evtype='w'
    UPDATE [events]
    SET evtypeid=3 WHERE evtype='s'
END

PRINT 'Dropping events.evtype column'
IF COL_LENGTH('events', 'evtype') IS NOT NULL
BEGIN
    ALTER TABLE events
    DROP COLUMN evtype
END

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You have a poor design if you are removing columns. But to solve your problem use dynamic SQL:

IF EXISTS (SELECT 1 FROM sys.columns 
           WHERE Name = N'evtype' AND Object_ID = Object_ID(N'events')
          )
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = '
UPDATE events
    SET evtype = (CASE WHEN evtype = ''d'' THEN 1
                       WHEN evtype = ''w'' THEN 2
                       WHEN evtype = ''s'' THEN 3
                  END)
    WHERE evtypeid IN (''d'', ''w'', ''s'')
';

    exec sp_executesql @sql
END;

This will prevent the compilation error because the column does not exist.

Upvotes: 1

Related Questions