How to trap errors in SQL Scripts

Question

We use scripts to add columns to our data tables like the one below. If the column already exists in the table, then the script crashes. Is there a way to trap this error so that script does not crash?

/* Add new column to dbo.cldoctype */
set xact_abort on
begin transaction

ALTER TABLE cldoctype ADD packet varchar(10) NULL

commit transaction

Answer 1

Try the following:

begin transaction
if not exists (select 1 from INFORMATION_SCHEMA.Columns where table_schema = 'dbo' and table_name='clDocType' and column_name = 'packet') -- check for column's existence

       ALTER TABLE cldoctype ADD packet varchar(10) NULL

 commit transaction

Answer 2

NaomiNNN,

I copied and pasted your code into my SSMS and received two errors: schema_name - Invalid column name 'schema_name' and 1. - Incorrect syntax near '1.'

How do I fix this?