Re: DEFAULT doesn't work in COALESCE?
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/09/05
- Next message: JT Lovell: "JOIN not returning desired value, please help!"
- Previous message: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- In reply to: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- Next in thread: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- Reply: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 8 Jan 2005 21:00:34 -0600
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 - drsql@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" <Vern@discussions.microsoft.com> wrote in message news:6BB1A4A1-B4D6-4582-8DE6-7F016CA7A60E@microsoft.com... > 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 > > "Gert-Jan Strik" wrote: > >> 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 wrote: >> > >> > 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 >> > >> > "Louis Davidson" wrote: >> > >> > > 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 - drsql@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" <Vern@discussions.microsoft.com> wrote in message >> > > news:6B9754E0-B8E9-4F0F-A75B-C9CF429461E5@microsoft.com... >> > > > 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" wrote: >> > > > >> > > >> 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 - drsql@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" <Vern@discussions.microsoft.com> wrote in message >> > > >> news:45EE8D32-45E0-46CA-B49B-3EE545C378C6@microsoft.com... >> > > >> > 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? >> > > >> > >> > > >> > >> > > >> >> > > >> >> > > >> >> > > >> > > >> > > >>
- Next message: JT Lovell: "JOIN not returning desired value, please help!"
- Previous message: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- In reply to: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- Next in thread: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- Reply: Vern: "Re: DEFAULT doesn't work in COALESCE?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
