Re: add column if it does not exist

On Mar 22, 12:01 pm, b...@xxxxxxxxxx wrote:
On Mar 22, 7:52 am, alacr...@xxxxxxxxx wrote:



If a column does not already exist on a table I want to add it. The
column already exists in the database I am testing this in.

Here is my problem:

IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
END

results in:
Error: com.sybase.jdbc3.jdbc.SybSQLException: Column names in each
table must be unique. Column name 'Status' in table 'Person' is
specified more than once.
, SQL State: ZZZZZ, Error Code: 2705

However
IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
ALTER TABLE Person
ADD Status char(1) NULL
END
results in:
Query 1 of 1 elapsed time (seconds) - Total: 0, SQL query: 0, Building
output: 0 (success)

Your solution would be to use dynamic SQL for the DDL. The error is
being raised during normalizations (as the parse tree is being
converted into a normalized query tree) rather than at execution. The
contents of dynamic sql aren't processed until they are actually
called, avoiding the error.

IF NOT EXISTS(SELECT 1
FROM sysobjects,
syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'Person'
AND syscolumns.name = 'Status')
BEGIN
EXECUTE (" ALTER TABLE Person
ADD Status char(1)
DEFAULT 'E' NOT NULL
")
END


That worked. Thank you both for your responses.

.

Relevant Pages

  • Re: add column if it does not exist
    ... FROM sysobjects, ... ALTER TABLE Person ... Your solution would be to use dynamic SQL for the DDL. ...
    (comp.databases.sybase)
  • add column if it does not exist
    ... FROM sysobjects, ... ALTER TABLE Person ... , SQL State: ZZZZZ, Error Code: 2705 ...
    (comp.databases.sybase)
  • Re: add column if it does not exist
    ... column already exists in the database I am testing this in. ... FROM sysobjects, ... ALTER TABLE Person ...
    (comp.databases.sybase)
  • Re: How Do You Get The Description Property?
    ... Note that you should avoid using sysobjects, syscolumns, systypes etc. ... I plan to add this to another sql statement which uses ...
    (microsoft.public.sqlserver.server)
  • Re: Ansi Nulls and quoted identified
    ... FROM sysobjects WHERE xtype IN ... ALTER PROCEDURE / ALTER VIEW / ALTER FUNCTION. ... lucky today, as to change an undocumented column in a system table), I ...
    (comp.databases.ms-sqlserver)
Loading