Saturday, August 17, 2013

Adding Column to SQL Server using Database Defensive Programming Technique

Recently I have learned good way to add column to sql server using database defensive programming technique from my co-worker.  All the credit for this blog post goes to him.  Thank you sir incase you are reading this blog post. (I have purposefully avoid mentioning name of co-worker due to privacy reason.)

Following example is very simple and self explanatory, Incase if you didn't get anything then pass your comment in comment section.


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

2 comments:

Unknown said...

i'm using visual studio 2010;
and sql server 2008 for database managment;

how i can open database in studio and access that database...

Vip said...

hii i am using sql server 2008 R2
i have a problem, i dont know how i can open import export data.(sql enterprise)

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape