DEFAULT doesn't work in COALESCE?

[Vern]

I'm trying to insert values into a column using a parameter. If the
parameter is not passed or is NULL, I'd like to use the DEFAULT value defined
for that column. I've tried to do the following, but it gives me a syntax
error:

INSERT State(State, Name, DisplaySeq)
VALUES (@State, @Name, COALESCE(@DisplaySeq, DEFAULT))

If I change the DEFAULT keyword to 0, then it works fine.
So how do I get the DEFAULT to work automatically?

[Anith Sen]

DEFAULT keyword stands by itself, it cannot be used in an expression. You
can use the metadata information from the catalogs to get the default value,
but using the value directly in the INSERT statement would be easier.

If you are using a stored procedure, you can check for NULLs prior to the
insert statements & do the substitution upfront.

--
Anith

[Louis Davidson]

I don't think you can do this. Default basically is the same thing as
leaving it blank, and it won't let you say if @variable is null then leave
it blank.

You could get the value of the default:

declare @default int
select @default = substring (column_default, 2,len(column_default) - 2) from
information_schema.columns
where table_schema = 'dbo'
and table_name = 'state'
and column_name = 'DisplaySeq'

INSERT State(State, Name, DisplaySeq)
VALUES (@State, @Name, COALESCE(@DisplaySeq, @default))

But I would probably just lump it and hard code this value, especially if
this is the only place where you create states.

--
----------------------------------------------------------------------------
Louis Davidson - dr...@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

"Vern" <Ve...@discussions.microsoft.com> wrote in message
news:45EE8D32-45E0-46CA...@microsoft.com...

[Vern]

Could I create a UDF to get the default value, and put that UDF into the
COALESCE list. For example, would this work?
INSERT State(State, Name, DisplaySeq)
VALUES (@State, @Name, COALESCE(@DisplaySeq, getDft(State,DisplaySeq))

And does anyone have a UDF that retrieves the default for a column in a table?

[Hugo Kornelis]

On Fri, 7 Jan 2005 09:03:09 -0800, Vern wrote:

>Could I create a UDF to get the default value, and put that UDF into the
>COALESCE list. For example, would this work?
>INSERT State(State, Name, DisplaySeq)
>VALUES (@State, @Name, COALESCE(@DisplaySeq, getDft(State,DisplaySeq))
>
>And does anyone have a UDF that retrieves the default for a column in a table?

Hi Vern,

Though this should work, it won't be fast. Why do you want to do it this
way? Why not simply
IF @DisplaySeq IS NULL
INSERT State (State, Name, DisplaySeq)
VALUES (@State, @Name, DEFAULT)
ELSE
INSERT State (State, Name, DisplaySeq)
VALUES (@State, @Name, @DisplaySeq)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Vern]

I thought about that, but if I had ten fields in the file with defaults,
there would be too many combinations of fields where each combination would
require its own insert statement.

The reason I'm doing this is to make a utility that generates a set of
standard procedures to insert,update,select,delete the records in a table for
all tables in a database. The code to do this is from
http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx

Its supposed to handle defaults, but isn't quite returning the correct
string in the COALESCE statement. I need to fix their code to just put the
actual default value into the list. But before I did this, I thougth I'd see
if there was a better way. Being new at this, doesn't help since I'll try
something that seems like it should work, only to find out that it doesn't
work. That's where this group has been invaluable! When I know it can't be
done a certain way, I can stop beating my head, and start looking for an
alternative. It's too bad DEFAULT can't be used in the COALESCE list.

[Vern]

This will be the only place that file is created, so I'll do that. I've just
been trying to use a utility that automatically creates this stored procedure
for any table, so I need to retrieve the default for any field in any table,
then use that actual default value in the COALESCE string. I can use your
example of how to get the default and compare it to how they were getting the
default. For more details, see my response to Hugo.

[Vern]

The routine you showed me is the same as the routine from the other source
and it returns the same invalid default. The default value in the
COLUMN_DEFAULT is
" CREATE DEFAULT df_DisplaySeq AS 0 "

Is this some kind of bug in .NET that it is returning the string that was
used to create the default instead of the actual default of 0?

[Louis Davidson]

Good idea. I didn't think of this because it was so obvious (and because
like one of Pavlov's subjects, it seems wrong because of how SQL Statements
behave when you use this branching construct.) The default value is easy
to get too from information schema (already posted elsewhere) if there is
more than one of these conditions you are trying to meet.

--
----------------------------------------------------------------------------
Louis Davidson - dr...@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:70htt0hkshaht179m...@4ax.com...

[Louis Davidson]

What do you want it to say? I thought you wanted the value. Perhaps we are
talking across purposes?

If you want the string for the default

select column_default

from information_schema.columns
where table_schema = 'dbo'
and table_name = 'state'
and column_name = 'DisplaySeq'

This includes the parens.

--
----------------------------------------------------------------------------
Louis Davidson - dr...@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

"Vern" <Ve...@discussions.microsoft.com> wrote in message

news:6B9754E0-B8E9-4F0F...@microsoft.com...

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Despite its faults - why not dynamic SQL?

CREATE PROC ... etc.

DECLARE @columns VARCHAR(250)
DECLARE @values VARCHAR(250)

IF @State IS NOT NULL BEGIN
SET @columns = 'State,'
SET @values = '@State,'
END

IF @Name IS NOT NULL BEGIN
SET @columns = @columns + 'Name,'
SET @values = @values + '@Name,'
END

... etc .

- -- remove trailing comma
IF LEN(@columns)>0 BEGIN
SET @columns = LEFT(@columns, LEN(@columns)-1)
SET @values = LEFT(@values, LEN(@values)-1)
END

IF LEN(@columns)>0 BEGIN
EXEC ( 'INSERT INTO table_name (' + @columns + ')
VALUES (' + @values ')' )
END

To avoid SQL injection attacks there should be validation checks on all
parameters.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd71r4echKqOuFEgEQLzJgCbBb6kYxmsemVkYWl5Y66/4WOn2NAAni2h
RQfvKHz/A3fAGi1wDtPgFpEp
=sJZ+
-----END PGP SIGNATURE-----

[Vern]

I think I have it narrowed down now.
If it is a character field that defaults to Y,
the value in the COLUMN_DEFAULT field is
"('Y')"
If it is a numeric tinyint field,
the value in the COLUMN_DEFAULT field is

" CREATE DEFAULT df_DisplaySeq AS 0 "

So your substring correctly returns Y for the character field, but I wanted
it to return 0 for the numeric field. I'll just have to use substring to
extract the zero, unless you know a better way.

Thanks

[Gert-Jan Strik]

Vern,

It seems the problem default is the one that was created by binding a
Default object to the column. IMO this is a Microsoft bug. The
workaround would be to not use binded defaults, but only Default
Constraints. (ALTER .. ADD CONSTRAINT DF_.. DEFAULT .. FOR ..)

See below for a repro script. On my SQL2K it returns this:

Default bound to column.
column_default
---------------------------
(0)
(0)
create default Zero as 0

(3 row(s) affected)

Repro script:

create table Test
(id tinyint not null constraint DF_Test_id default 0
,id2 tinyint null
,id3 tinyint null)
go
alter table test add constraint DF_Test_id2 default 0 for id2
go
create default Zero as 0
go
sp_bindefault Zero,'Test.id3'
go

select column_default
from information_schema.columns
where table_schema = 'dbo'

and table_name = 'Test'
and column_name like 'id%'
go
drop table test
go
drop default Zero

Hope this helps,
Gert-Jan

[Vern]

Yes, that is the difference. I went back and looked at the table column
definitions, and if I just put (0) as the default for the field using
Enterprise Manager, it is fine, but if I create a standard default for the
field, and then reference that, then it returns the reference to the standard
default along with the default value. Guess I should do like you're saying
until they fix this.

Thanks

[Louis Davidson]

Actually they are more likely to drop the concept of bound defaults.
Constraints are the standard way to do this, and really should be the way
you create defaults anyhow.

--
----------------------------------------------------------------------------
Louis Davidson - dr...@hotmail.com
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

"Vern" <Ve...@discussions.microsoft.com> wrote in message

news:6BB1A4A1-B4D6-4582...@microsoft.com...

[Vern]

Thanks, hopefully they'll change the Enterprise Manager to set up constaints
for the defaults when one specifies the default for a field. I like their
idea of creating named defaults that you can select from a drop down list so
you can have a standard default for a field.

[--CELKO--]

>>COALESCE(@DisplaySeq, DEFAULT)) <<

The engine needs to know which column the DEFAULT is coming from and it
cannot find out when DEFAULT is a parameter. The simple INSERT INTO
can match to a column.