Default value of an attribute - SQL Server Forums

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

Database Design and Application Architecture

Default value of an attribute

Author

Topic

X-Force
Starting Member

1 Post

Posted - 2009-08-22 : 12:42:23

How to set the default value of an attribute to the value of another attribute in another table using SQL Visual Database Tools?

dportas
Yak Posting Veteran

53 Posts

Posted - 2009-08-24 : 15:29:51

Use a user-defined function

CREATE FUNCTION dbo.Foo ()
RETURNS INT
AS
BEGIN;
RETURN
(SELECT x
FROM dbo.Tbl1);
END;
GO

ALTER TABLE Tbl2 ADD DEFAULT (dbo.Foo()) FOR x;

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 09:42:56

quote:
Originally posted by dportas

Use a user-defined function

CREATE FUNCTION dbo.Foo ()
RETURNS INT
AS
BEGIN;
RETURN
(SELECT x
FROM dbo.Tbl1);
END;
GO

ALTER TABLE Tbl2 ADD DEFAULT (dbo.Foo()) FOR x;

Exactly. Two caveats here:

You cannot do this completely with the "visual" tools; must drop down to writing the T-SQL function and then you can go back to the visual tools
Remember that this is a scalar function and, as such, requires a single value. You cannot use TSQL statements that return a result set (like the code sample does); your function must return a single, scalar value.

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-05 : 06:40:31

the function should be like


CREATE FUNCTION dbo.GetDefault ()
RETURNS sql_variant
AS
BEGIN
SELECT d.definition FROM sys.default_constraints d
INNER JOIN sys.columns c
ON d.parent_column_id = c.column_id
WHERE d.parent_object_id = OBJECT_ID(your reference table', N'U')
AND c.name = 'your reference column'
END

and then use it like

ALTER TABLE Tbl2 ADD DEFAULT (dbo.GetDefault ()) FOR x;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources