Remove default value on column in SQL table before setting a new one

Hello Experts,

I am using dynamic SQL in the following SPROC to set the default value in a tables column. This works very nicely as long as there has not already been a defult value set.

This is the Error message coming from SQL Server when I run this SPROC on a table that already has a default value in the clomumn:

"Column already has a DEFAULT bound to it.\r\nCould not create constraint. See previous errors."}      System.Exception {System.Data.SqlClient.SqlException}

I can do this by creating and deleting temporary tables (Add the new default to a temp table, insert data into the temp table, copy date from temp table to main, drop and create temp  table etc) but this is cumbersome.

 Is there a way to delete the Default value first before running the SPROC to add a new one??

Thanks!


ALTER PROCEDURE AddDefaultTripNumSP

 @TripNum varchar(10),
 @TableName nvarchar(64),
 @SQL VARCHAR(1000)
 AS

--Dynamic SQL
      SET @TripNum = '2007B'
      SET @TableName  = 'dbo.ObsData'
     
      SET @SQL = 'ALTER TABLE ' + @TableName + ' WITH NOCHECK ADD DEFAULT ''' +  @TripNum + ''' FOR TripNum'

      EXEc(@SQL)
GO