November 17, 2005 at 6:10 am
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
November 17, 2005 at 7:37 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2005 at 7:38 am
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?
November 17, 2005 at 11:45 am
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
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply