CODEWITHSUNDEEP
sqlsybasesap-ase
espresso_coffee
espresso_coffee

Reputation: 6110

Check if column exists then alter column from the table?

I want to write sql script that should check if column exists in the table, and then remove the column if previous statement is true. The database I use is Sybase ASE, and this is the code that I tried to use:

IF EXISTS (SELECT 1 FROM syscolumns WHERE id = object_id('users') AND name = 'maiden_name')
    BEGIN
        ALTER TABLE security DROP maiden_name
    END

The code above executed successfully first time I run it. The second time I goth the error:

Invalid column name 'maiden_name'

If column does not exist the ALTER TABLE block of code shouldn't run. Is there a way to achieve this is Sybase? Thank you.

Upvotes: 1

Views: 2791

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use dynamic SQL:

IF EXISTS (SELECT 1 FROM syscolumns WHERE id = object_id('users') AND name = 'maiden_name')
    BEGIN
        EXEC('ALTER TABLE security DROP maiden_name')
    END;

The problem is that the parser is trying to parse the ALTER during the compilation phase, and it gets an error if the column does not exist.

Upvotes: 4

Related Questions