Change the default value of a collumn with a variable

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

 

 

 

Answer 1

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

Answer 2

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