question

JeffStiegler-8414 avatar image
0 Votes"
JeffStiegler-8414 asked NaomiNNN commented

How to trap errors in SQL Scripts

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
sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffStiegler-8414 avatar image
0 Votes"
JeffStiegler-8414 answered NaomiNNN commented

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?


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, should be table_schema instead (I wrote from memory).

0 Votes 0 ·

That worked! But still have an issue with the '1. ALTER TABLE.....' line of code. '1.' produces error: Incorrect syntax near '1.' Script will not run because of this.

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN JeffStiegler-8414 ·

Can you post the code you currently have for spot check? I don't see the error now myself, the syntax seems fine to me. I know I used this technique lots of times.

0 Votes 0 ·