Default Value and Null - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Default Value and Null

Author

Topic

zaurska
Starting Member

12 Posts

Posted - 2008-08-23 : 15:36:23

This is a "I'm sure I'm being dumb" question.

Table columns

ID (PK)
Name (default = "undefined") allow Nulls: NO
Age (default = "18") allow Nulls: NO

so I know I can INSERT and pass DEFAULT as the value and the DEFAULT will be used.

But I want my user to sometimes provide values, or sometimes not to. And if Null is the parameter passed, for the default to be used.

I thought this was the purpose of the default value really.

But, if I execute:

Declare @nameParam nchar(10)
Declare @ageParam int

Set @nameParam ='bob'

INSERT INTO testINSERT
(nameValue, age)
VALUES (@nameParam,@ageParam)

i get an error for the NULL

If I use, for example MS SQL MGT SERVER Express, and Open Table and key into the table and leave a value Null, it inserts and uses my Default.

What sort of Insert is "it" using, and what could I use?

Help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-23 : 16:09:42

It's because @ageParam equals NULL, since you haven't set it to anything. You are passing it a NULL. Check this:

Declare @nameParam nchar(10)
Declare @ageParam int

Set @nameParam ='bob'

SELECT @nameParam AS NameParam, @ageParam AS AgeParam

If you want to use the default value, then do this:

Declare @nameParam nchar(10)

Set @nameParam ='bob'

INSERT INTO testINSERT(nameValue)
VALUES (@nameParam)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zaurska
Starting Member

12 Posts

Posted - 2008-08-23 : 16:43:42

Yes, sorry Tara; but that was my point. I didn;t declare @ageParam to simulate a type of input.

Sometimes my user will leave the age Null, sometimes they'll enter it.

How do I allow both types of input to be handled?

One time someone will enter: Bob 32
Another time someone will enter: Bob Null

on the second version I want the default to be used...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-23 : 17:41:59

In your application code, check for the NULL value and then do this:

INSERT INTO testINSERT(nameValue, ageValue)
VALUES(@nameParam, DEFAULT)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zaurska
Starting Member

12 Posts

Posted - 2008-08-24 : 07:37:45

I need to do it in the DB.

This seems to work:

Declare @nameParam nchar(10)

Set @nameParam ='bob'
Declare @ageParam int

/*if the parameter is null fetch & CAST the default*/
Set @ageParam = (isnull(@ageParam,

CAST(Substring(
(SELECT
definition
FROM sys.tables AS ST
INNER JOIN
sys.syscolumns AS SC ON ST.object_id = SC.id
INNER JOIN
sys.default_constraints AS SD ON ST.object_id =
SD.parent_object_id
AND SC.colid = SD.parent_column_id

WHERE id = object_id('testINSERT') and parent_column_id=3)
,3,2) AS int)

))

Select @ageParam

INSERT INTO testINSERT (nameValue,age)
VALUES
(@nameParam, @ageParam)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-24 : 11:09:23

I don't see why you'd do that when my way works fine in the database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zaurska
Starting Member

12 Posts

Posted - 2008-08-26 : 04:29:59

Hi Tara

I wanted to do it in the database because I have 2* c# applications and 1* c++ application accessing the database and it seems more logical only to have to maintain this function in one place rather than 3.

The actual table I need this for has 24 columns with sometimes provided Null sometimes populated. I had developed and signed off the apps assuming the default binding behaviour of MS SQL MS was SQL based rather than simply on the interface.

Thanks for your advice, I'll know next time to write the application's code as you suggest.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-26 : 04:33:25

INSERT INTO testINSERT
(nameValue, age)
VALUES (@nameParam,isnull(@ageParam, 18))

E 12°55'05.25"
N 56°04'39.16"

zaurska
Starting Member

12 Posts

Posted - 2008-08-26 : 04:57:21

much neater, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources