Setting value to the default specified in the table definition

Question

Suppose I have a table (all names are for illustration purposes, the original names have been withheld):

CREATE TABLE [dbo].[tblSomeName](
    [ChangeDate] [DATE] NOT NULL,
    [ID] [INT] NOT NULL,
    [Source] [VARCHAR](100) NOT NULL DEFAULT ('Manual')
)

I would like to write a stored proc like:

CREATE PROC p_PutToTable
(
    @ID [INT], 
    @ChangeDate [date] = NULL,
    @Source [varchar] (100) = NULL
)
AS BEGIN
  MERGE tblSomeName as tgt
  USING (SELECT ...

If the @Source is NULL I would like to have the stored proc use the default from the table. I would like to avoid hard-coding the default specified in the table in yet another place, i.e. this stored proc. Maybe somehow query the default from the table definition? (Hoping there's a better way)

Sean Lange · Accepted Answer · 2017-11-09 15:55:59Z

1

If you can create your default so that is has a name this becomes pretty easy. You could do by changing the way you create your table.

CREATE TABLE [dbo].[tblSomeName](
    [ChangeDate] [DATE] NOT NULL,
    [ID] [INT] NOT NULL,
    [Source] [VARCHAR](100) NOT NULL
)

alter table tblSomeName
add constraint DF_tblSomeName DEFAULT ('Manual') FOR [Source]

Now we know with certainty what the name of the constraint is so we can easily find it.

Your procedure could be something like this.

CREATE PROC p_PutToTable
(
    @ID [INT], 
    @ChangeDate [date] = NULL,
    @Source [varchar] (100) = NULL
)
AS BEGIN


    if @Source IS NULL
        select @Source = replace(replace(replace(definition, '(', ''), ')', ''), '''', '')
        from sys.default_constraints
        where name = 'DF_tblSomeName'

    --The rest of your code here

END

answered Nov 9, 2017 at 15:55

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Denis Over a year ago

I like this idea. This is great, didn't think of it.

Bohemian Over a year ago

No, you don't know with certainty what the constraint name is.This is not a good solution. What if the default was defined in-line with the column definition? What if someone drops the constraint and creates a new one with a different name? A good solution would be to query knowing only the column and table names.

Sean Lange Over a year ago

@Bohemian - In the example posted I absolutely do know what the name of the constraint is. I agree that a better solution would be to query using the column and table names but that doesn't mean this is a bad solution.

Bohemian Over a year ago

@sean it's "bad" because it's brittle, and importantly if the name changes no errors will be thrown and the behaviour of the system will silently change

Sean Lange Over a year ago

@Bohemian I agree it is brittle. But the same argument you make about the name of the constraint can be made the other way. If you change the query to look at the constraints by table and column name it could just as easily break if either of those is changed. Seems that isn't really any better. ;)

|

Collectives™ on Stack Overflow

Setting value to the default specified in the table definition

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related