User defened Datatypes Default values.
-
Dear All
I would like to know the syntax of adding Default value during UDT creation (Rather than by using sp_default bind procedure)
eg when i try CREATE TYPE [XyzUDT] FROM [INTEGER) DEFAULT 0 NOT NULL it gives error.
Thank and regards
-
did you check with books online ?
Create type:
http://msdn.microsoft.com/en-us/library/ms175007(v=sql.105).aspx
My advice: Avoid like hell because they cannot be altered when they are in use. It's not all gold that shines
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I tried but it gives error message incorrect synatx near default
CREATE TYPE a FROM integer NOT NULL DFAULT 1;
-
if you copy/pated the statement in your previous reply ...
you typed dfault in stead of dEfault.
Chances are you overlooked the typing error.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
In the original example it may also because you have a round bracket instead of a square one at the end of the INTEGER key word.
CREATE TYPE [XyzUDT] FROM [INTEGER ) DEFAULT 0 NOT NULL
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
This may be one of the cases where BOL is just wrong. :angry:
This does the trick you aim for:
Create default [MinusOne] as (-1);
go
/* Syntax error near Default --> CREATE TYPE [XyzUDT] FROM [INTEGER] NOT NULL with DEFAULT 0 ; */
CREATE TYPE [dbo].[XyzUDT] FROM [int] NOT NULL;
EXEC sys.sp_bindefault @defname=N'[dbo].[MinusOne]', @objname=N'[dbo].[XyzUDT]'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I know it works with sp_bind syntax. I wanted to do it with out it.
Also after correcting the typo error it still gives error "incorrect syntax near Default".
-
Binding a default value to a data type will be removed from SQL because it is a bad idea.
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you create default definitions by using the DEFAULT keyword of the ALTER TABLE or CREATE TABLE statements instead.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply