Table column with conditional default value

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

Table column with conditional default value

Author

Topic

Ally
Starting Member

10 Posts

Posted - 2012-07-20 : 09:11:47

Hello,

Is it possible to create a table that has a column with conditional default value?

Like :

CREATE TABLE [dbo].[T_Test](
[aaa] [varchar](2) NULL,
[bbb] [int] NULL,
[ccc] [int] NULL,
[ddd] [int] NULL,
[eee] AS (([ccc] - [ddd]), ---calculated column
[ggg] [varchar](1) (IF [eeee] > 0 then ‘P’ else ‘F’)
) ON [PRIMARY]

Any help is greatly appreciated,

Thanks
Ally

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 10:02:31

the query above create a table (test) and put getdate() as default value in column date:

CREATE TABLE [dbo].[Test](
[date] [datetime] NULL,
[other] [int] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Test] ADD CONSTRAINT [DF_Data1_date] DEFAULT (getdate()) FOR [date]
GO

------------------------
PS - Sorry my bad english

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 10:13:51

you can like below


CREATE TABLE [dbo].[T_Test](
[aaa] [varchar](2) NULL,
[bbb] [int] NULL,
[ccc] [int] NULL,
[ddd] [int] NULL,
[eee] AS (([ccc] - [ddd]), ---calculated column
[ggg] AS CASE WHEN [eeee] > 0 then 'P' else 'F' END 
) ON [PRIMARY]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 10:18:46

I don't know if that would do it, Visakh. For one thing, computed column using a computed column may not be pleasing to SQL Server. But that can be easily fixed by replicating the formula instead of using the formula for column eee.

But if I understood the OP correctly, what they are looking for is default value (which can probably be changed later to something else altogether), rather than a computed column.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-20 : 10:35:31

A default constraint can't accept column names as part of the expression. You'd have to use an INSERT trigger to set the initial value based on that calculation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 10:38:46

quote:
Originally posted by sunitabeck

I don't know if that would do it, Visakh. For one thing, computed column using a computed column may not be pleasing to SQL Server. But that can be easily fixed by replicating the formula instead of using the formula for column eee.

But if I understood the OP correctly, what they are looking for is default value (which can probably be changed later to something else altogether), rather than a computed column.

ah my bad
didnt notice that [eee] itself was a computed column. In this case yep i do agree, you can just replace them with actual formula.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ally
Starting Member

10 Posts

Posted - 2012-07-20 : 12:02:21

Hi, It works.

Thank you / Thanks everyone.

Ally

quote:
Originally posted by visakh16

you can like below

CREATE TABLE [dbo].[T_Test](
[aaa] [varchar](2) NULL,
[bbb] [int] NULL,
[ccc] [int] NULL,
[ddd] [int] NULL,
[eee] AS (([ccc] - [ddd]), ---calculated column
[ggg] AS CASE WHEN [eeee] > 0 then 'P' else 'F' END
) ON [PRIMARY]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources