Defining a default value, how to?

  • Hi there,

    I like to define a default value for a column in a table, which is the MAX(C1) from another table. I was hoping I could do this without a trigger, so I tried first to use a function, for this is to be implemented in many tables.

    A function cannot be giving in the DEFAULTVALUE property of the column. The I used the statement:

    SELECT max(C1) from Table1

    No dice, even without the select and preceded with an =.

    Can I only give a static value in the defaultvalue property? Any other way I could accomplish this? Do I miss something here?

    Greetz,
    Hans Brouwer

  • You are wrong in stating that functions cannot be used as defaults - getdate() works fine, for example.

    What can be used - from BOL:

    "Is an expression that contains only constant values (it cannot include the names of any columns or other database objects). Any constant, built-in function, or mathematical expression can be used. Enclose character and date constants in single quotation marks ('); monetary, integer, and floating-point constants do not require quotation marks. Binary data must be preceded by 0x, and monetary data must be preceded by a dollar sign ($). The default value must be compatible with the column data type."

    So what you want cannot be accomplished through the use of a DEFAULT.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I don't think there's an easy way to do this in T-SQL. Not sure there's a great way. I typically definite defaults and then assign them, that way they can be resused.

    Why does a static value not work?

  • I agree with Steve, there's not an easy way to do this.  However, if you want to create a programmatic way to assign this default value, you could force all insertsto be done through a stored procedure and set the default there:

    CREATE PROC my_proc

    (@val1 VARCHAR(25), @val2 VARCHAR(25))

    AS

    DECLARE @max-2 INT

    SET @max-2 = (SELECT max(C1) from Table1)

    INSERT INTO my_table (val1, val2, val3)

    VALUES (@val1, @val2, @max-2)

    GO

    A similar proc could be utilized for updates if necessary.

    Hope this helps.

    ~Tim

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply