2

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.

GISmatters
  • 431
  • 8
  • 20

1 Answers1

9

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Ah! So simple! I suspected that I was overcomplicating things... and thanks for the confirmation that dynamic SQL is the only way to automate the column selection. – GISmatters Apr 01 '11 at 16:24
  • Thanks for the followup script, which is a bit more robust than the one I just wrote. Cheers. – GISmatters Apr 01 '11 at 16:43