locked
Change the default value of a collumn with a variable RRS feed

  • Question

  • Is there anyway to change your default value of a collumn on a modular way (and a clean one)

    for example in a stored procedure.

     

    create table del_test(
    id smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
    name varchar(100),
    description varchar(100),
    area varchar(100) CONSTRAINT default_area default 'tr1'
    );
    
    CREATE PROCEDURE import_del_test @areavalue nvarchar(100) 
    AS
    ALTER TABLE del_test DROP CONSTRAINT Default_area;
    ALTER TABLE del_test ADD CONSTRAINT Default_area DEFAULT @areavalue FOR area;
    go

     

     

     

    Friday, December 11, 2009 5:44 PM

Answers

  • You could do it using dynamic sql as in the sample below.  I am curious as to why you want to change a table default on the fly. By the procedure name, I gather you're importing data - but wouldn't it be easier to just ensure that when you're inserting the data, if the value is NULL or missing, insert the default value being passed to the SP?

    CREATE PROCEDURE import_del_test @areavalue nvarchar(100) 
    AS
    BEGIN
    DECLARE @SQL NVARCHAR(200)
    
    SET @SQL = 'ALTER TABLE del_test ADD CONSTRAINT Default_area DEFAULT '''
    	+ @areavalue + ''' FOR area'
    	
    ALTER TABLE del_test DROP CONSTRAINT Default_area;
    EXEC sp_executesql @SQL
    
    END
    go
    
    • Marked as answer by pripo Friday, December 11, 2009 7:13 PM
    Friday, December 11, 2009 6:09 PM

All replies

  • You could do it using dynamic sql as in the sample below.  I am curious as to why you want to change a table default on the fly. By the procedure name, I gather you're importing data - but wouldn't it be easier to just ensure that when you're inserting the data, if the value is NULL or missing, insert the default value being passed to the SP?

    CREATE PROCEDURE import_del_test @areavalue nvarchar(100) 
    AS
    BEGIN
    DECLARE @SQL NVARCHAR(200)
    
    SET @SQL = 'ALTER TABLE del_test ADD CONSTRAINT Default_area DEFAULT '''
    	+ @areavalue + ''' FOR area'
    	
    ALTER TABLE del_test DROP CONSTRAINT Default_area;
    EXEC sp_executesql @SQL
    
    END
    go
    
    • Marked as answer by pripo Friday, December 11, 2009 7:13 PM
    Friday, December 11, 2009 6:09 PM
  • The only way i can use a default value during a bulk insert is with a default contstraint.

    During another run afterwards I want to change this value. that's why i create a stored procedure that does the changing of the constraint and the bulk insert in once.

    thx

    if there is a better way to do... please don't hesitate

    Friday, December 11, 2009 7:15 PM