Technical Article

spDropField

,

spDropField does exactly the opposite of spAddField.  It checks for existence, then drops it, if it does exist.

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating