How to "Look Up" the Default Value for a Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to "Look Up" the Default Value for a Column

Author

Topic

jtoddspradlin
Starting Member

1 Post

Posted - 2006-12-25 : 04:07:24

I would like to place a statement in the default value property for a column to "look up" the value in another table. When I placed a SELECT statement in the default value property enclosed in parentheses I received an error stating that scalar subqueries are not permitted only scalar expressions. How can I achieve this?

TABLE 1: RecordNumber, PatientName, InsurerID
TABLE 2: InsurerID, InsurerName

By default I would like new records in Table 1 to set InsurerID to whichever InsurerID in Table 2 has the InsurerName = 'Unknown."

Thanks,
Todd

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-25 : 04:22:11

Default definition can not include queries, it must be a constant expression. What you are trying to achieve is only possible by writing Insert Trigger on the Table1.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 04:32:39

I am not sure on this, but can we set UDF has a default value to the column??

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-25 : 06:09:45

No, I don't think SQL Server allows it.

As per BOL, default can only be constant expression or system function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 06:20:45

Aha i got a suprise when i read BOL for SQL SERVER 2005

quote:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use default definitions created by using the DEFAULT keyword of ALTER TABLE or CREATE TABLE.

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources