SQL Server Hosting – How to Add Column to SQL Server using Defensive Database Programming?

Peter Cole | October 28, 2015 | SQL 2016 Hosting | No Comments

Now, we want to learned a good way to add column to your sql server using Defensive Database Programming. Defensive database programming is to produce resilient database code; in other words code that does not contain bugs and is not susceptible to being broken by unexpected use cases, small modifications to the underlying database schema, changes in SQL Server settings, and so on. The following code is very simple and self explanatory. Now write the below code:

BEGIN TRANSACTION
IF EXISTS(SELECT 1 from information_schema.tables
          where table_name = 'MyTableName')
  BEGIN
    Print('Table Exist');

    --Add Column MyColumn
    IF NOT EXISTS(SELECT 1 from information_schema.columns
                  where table_name = 'MyTableName'
                  and Column_Name='MyColumn')
     BEGIN
 ALTER TABLE MyTableName ADD MyColumn varchar(345) NULL;
 Print('MyColumn Column Added');
     END

    ELSE
     
     BEGIN
 Print('MyColumn Column Already Exist');
     END
  END

Else
    BEGIN
  Print('Table does not Exist');
    END


IF @@ERROR <> 0
    BEGIN
        PRINT('Problem in running script, Rolling back');
        ROLLBACK TRANSACTION;
    END
ELSE
 BEGIN
  PRINT('Script Run Successfully');
        COMMIT TRANSACTION;
 END

BEGIN TRANSACTION

IF EXISTS(SELECT 1 from information_schema.tables

where table_name = 'MyTableName')

BEGIN

Print('Table Exist');

--Add Column MyColumn

IF NOT EXISTS(SELECT 1 from information_schema.columns

where table_name = 'MyTableName'

and Column_Name='MyColumn')

BEGIN

ALTER TABLE MyTableName ADD MyColumn varchar(345) NULL;

Print('MyColumn Column Added');

END

ELSE

BEGIN

Print('MyColumn Column Already Exist');

END

Else

BEGIN

Print('Table does not Exist');

END

IF @@ERROR <> 0

BEGIN

PRINT('Problem in running script, Rolling back');

ROLLBACK TRANSACTION;

END

ELSE

BEGIN

PRINT('Script Run Successfully');

COMMIT TRANSACTION;

END

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Related Posts

About The Author

Pete