Reset a subset of columns to their default values in SQL?

Question

I'm looking for a SQL statement or stored procedure to reset the values for a subset of columns in a row to their default values (where so defined) or else to null. There are other columns in the row that I don't want to reset, else I could just delete the row and create a new one which would be initialized with the various default values... that's the effect I want, but only for a particular subset of the columns.

Conceptually, I'm looking for

UPDATE eirProj 
SET <all columns named like 'paf%'> TO THEIR DEFAULT OR NULL
WHERE prjId=@prjId

or at least

UPDATE eirProj 
SET pafAuth=<pafAuth default or NULL>, pafComp=<pafComp default or NULL>, paf...
WHERE prjId=@prjId

I'm trying to avoid hard-coding the default values redundantly in two places (the table definition and this reset code). Ideally but less critically I'd like to avoid hard-coding column names so it would still work if the subset changes by adding, dropping, or renaming columns.

I'm working in SQL Server and T-SQL-specific solutions are ok if that's the only way to do this.

Answer 1

You can use the default keyword

CREATE TABLE dbo.eirProj
  (
     paf1 INT DEFAULT(100),
     paf2 INT NULL
  )

INSERT INTO dbo.eirProj
VALUES      (1,
             1)

UPDATE dbo.eirProj
SET    paf1 = DEFAULT,
       paf2 = DEFAULT

SELECT *
FROM   dbo.eirProj  

Returns

paf1        paf2
----------- -----------
100         NULL

There is no way of doing this for all columns called paf% unless you use dynamic SQL to generate the above.

DECLARE @Script NVARCHAR(MAX)

SELECT @Script = ISNULL(@Script + ',', '') + QUOTENAME(name) + ' =default'
FROM   sys.columns
WHERE  object_id = object_id('dbo.eirProj')
       AND name LIKE '%paf%'

IF ( @@ROWCOUNT = 0 )
  RETURN

SET @Script = 'UPDATE dbo.eirProj SET ' + @Script

EXEC(@Script)