spAddField

/**********************************************************
  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