/**********************************************************
  spGetFieldsExists
**********************************************************/

Print 'spGetFieldsExists'
go

If Exists (Select * From SysObjects
             Where ID = Object_ID('spGetFieldsExists')
               And ObjectProperty(ID, 'IsProcedure') = 1)
  Drop Procedure spGetFieldsExists
go

Create Procedure spGetFieldsExists 
  @TableName varchar(50),
  @FieldName varchar(50),
  @exists integer output
as

set NoCount On

    if exists 
      (
        select *
          from 
            SysObjects O, 
            SysConstraints C, 
            SysColumns L
          where ObjectProperty(O.ID, 'IsUserTable') = 1
            and O.ID = C.ID
            and O.ID = L.ID
            and L.Name like @FieldName
            and O.Name like IsNull(@TableName, O.Name)
      )
      set @exists = 1
    else
      set @exists = 0

go

Grant Execute On spGetFieldsExists To Public
go




/**********************************************************
  spAddField
**********************************************************/

print 'spAddField'
go

if
  exists
  (
    select * from SysObjects
      where ID = Object_ID('spAddField')
        and ObjectProperty(ID, 'IsProcedure') = 1
  )
begin
  drop procedure spAddField
end
go

create procedure spAddField
  @TableName varchar(50),
  @FieldName varchar(50),
  @FieldType varchar(50),
  @Null varchar(50)
as
  set NoCount on
  --try

    declare 
      @result int,
      @execStr NVarChar(1024),
      @exists int

    exec @result = spGetFieldsExists @TableName, @FieldName, @exists output
    if @@Error <> 0 or @Result <> 0 goto ErrorProc

    if ( @exists = 1 )
    begin
      select @execStr =
      'alter table ' + @TableName +
      '  drop column ' + @FieldName 

      exec @Result = sp_executesql @execStr
      if @@Error <> 0 or @Result <> 0 goto ErrorProc

      set @exists = 0      
    end

    if ( @exists = 0 )
    begin
      select @execStr =
      'alter table ' + @TableName +
      '  add ' + @FieldName + ' ' + @FieldType + ' ' + @Null

      exec @Result = sp_executesql @execStr
      if @@Error <> 0 or @Result <> 0 goto ErrorProc
    end


  --finally
    SuccessProc:
    return 0  /* success */

  --except
    ErrorProc:
    return 1 /* failure */

  --end
go

grant execute on spAddField to Public
go




/**********************************************************
  spDropField
**********************************************************/

print 'spDropField'
go

if
  exists
  (
    select * from SysObjects
      where ID = Object_ID('spDropField')
        and ObjectProperty(ID, 'IsProcedure') = 1
  )
begin
  drop procedure spDropField
end
go

create procedure spDropField
  @TableName varchar(50),
  @FieldName varchar(50)
as
  set NoCount on
  --try

    declare 
      @result int,
      @execStr NVarChar(1024),
      @exists int

    exec @result = spGetFieldsExists @TableName, @FieldName, @exists output
    if @@Error <> 0 or @Result <> 0 goto ErrorProc

    if ( @exists = 1 )
    begin
      select @execStr =
      'alter table ' + @TableName +
      '  drop column ' + @FieldName 

      exec @Result = sp_executesql @execStr
      if @@Error <> 0 or @Result <> 0 goto ErrorProc

      print 'Field ( '+@TableName+'.'+@FieldName+' ) Dropped'
    end
    else
    begin
      print 'Field ( '+@TableName+'.'+@FieldName+' ) does not exist'
    end


  --finally
    SuccessProc:
    return 0  /* success */

  --except
    ErrorProc:
    return 1 /* failure */

  --end
go

grant execute on spDropFieldto Public
go