Inserting default values with COALESCE

  • Thread starter Thread starter Steven Livingstone
  • Start date Start date
S

Steven Livingstone

Working on this just now, but maybe someone has a quick answer for me :)

I simly want to say that is the param is NULL, then use the default value.
This fails - some problem with default :S
"Incorrect syntax near the keyword 'DEFAULT'."

declare @someid INT
SET @someid = NULL

insert into mytable (id, otherid)
values (323, COALESCE(@someid, DEFAULT))


This works fine

insert into mytable (id, otherid)
values (323, DEFAULT)

Anyone?

regards,
Steven.
 
If you are just testing for one value, use ISNULL
(@someID, 'DEFAULT') where 'DEFAULT' is a literal. If
you have a default set up on the DB side, it will go in
there automatically if you insert null.

You may try setting the @someid with a coalesce statement
first ie set @someid = COALESCE(someField, 'DEFAULT')


Also, you can use Case
When @someid IS Null THen default
type syntax.

I'm going to be in the office in a few minutes,
syntactically your statement looks correct, let me see
what happens when I try it on my machine.

I'll repost shortly, but the above suggestions will
hopefully get you through the problem for right now.

Cheers,

Bill

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com
 
Just ignore the whole coalesce/isnull thing. If it's
null, it will accept the default value that your table
defined. However, if you want the literal 'DEFALUT' in
the column, then wrap it in single quotes.

I tried it with ISNULL and had the same problem. This is
pure conjecture on my part, but I think the problem may be
because since the default value of the column will be
entered anyway, the function signature won't allow it,
even if you escape it with []. This however is just a
guess. Nonetheless, you can definitely get around the
problem using either the case methodology or just leaving
the insert as is.

Good Luck,

Bill

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com
 
hmmm, so i figured out a way of doing it, but i hope there is a better
answer than to work around with the system tables....

/*==============================*/

--get default value from system tables
declare @Column nvarchar(50)
set @Column = 'Currency'

declare @return SMALLINT
set @return = (select info from syscolumns
JOIN sysobjects ON sysobjects.id = syscolumns.cdefault
WHERE syscolumns.name = @Column)

--perform update, adding default if needed
declare @someid INT
SET @someid = null

insert into tbl_currency (myid, currency)
values (1, coalesce(@someid, @return))

/*==============================*/

Anyone?
 
Back
Top