1 Version 4.0 -- 1/15/89 sp_addtype
______________________________________________________________________
NAME: sp_addtype
FUNCTION:
Creates a user-defined datatype.
SYNTAX:
sp_addtype typename, phystype[(length)] [, nulltype]
EXAMPLES:
1) sp_addtype ssn, "varchar(11)"
Creates a user-defined datatype called ssn to be used for
sp_addtype Version 4.0 -- 1/15/89 2
______________________________________________________________________
columns that hold social security numbers. Since no nulltype
parameter is specified, no null values are allowed. Notice
that varchar(11) is enclosed in quotation marks because it
contains punctuation (parentheses).
2) sp_addtype birthday, datetime, null
Creates a user-defined datatype called birthday that allows
null values.
PARAMETERS:
typename - is the name of the user datatype. Type names must
follow the rules for identifiers and must be unique for each
owner in each database.
phystype[(length)] - is the physical or SQL Server-supplied type
(char, int, etc.) on which the user datatype is based.
Length must be specified for char, varchar, binary, and
3 Version 4.0 -- 1/15/89 sp_addtype
______________________________________________________________________
varbinary. If a length is given, the phystype parameter must
be enclosed in single or double quotes. (Quotes are required
around all parameters with embedded blank spaces or punctua-
tion.)
nulltype - indicates how the user datatype handles null value
entries. The default (if this optional parameter is omitted)
is NOT NULL (disallow null values, require an explicit
entry). Acceptable values for this parameter are null, NULL,
nonull, NONULL, "not null", and "NOT NULL".
You can override the user datatype's nulltype condition in a
CREATE TABLE statement.
COMMENTS:
o Executing sp_addtype creates a user-defined datatype and adds
it to the systypes system table. Once a user datatype is
created, you can use it in CREATE TABLE statements and bind
sp_addtype Version 4.0 -- 1/15/89 4
______________________________________________________________________
defaults and rules to it.
o Define each user datatype in terms of one of the physical
(SQL Server-supplied) datatypes, optionally specifying NULL
(allow null entries) or NONULL (disallow them). The default,
if you give no third parameter, is nonull.
o A user-defined datatype name must be unique in the database,
but user-defined datatypes with different names can have the
same definitions.
MESSAGES:
The new user datatype was created and may now be used in CREATE
TABLE statements, or to bind rules and defaults.
Syntax summary. The nulltype parameter can be null, NULL,
nonull, NONULL, "not null", or "NOT NULL".
The typename parameter is already a user-defined datatype.
Choose a different name.
5 Version 4.0 -- 1/15/89 sp_addtype
______________________________________________________________________
The length of a datatype must be between 1 and 255.
The phystype you gave is not a SQL Server datatype.
The bit datatype doesn't allow null values and you specified with
the nulltype parameter that you wanted to allow null values.
The physical datatypes that take length specifications are char,
varchar, binary, and varbinary. Other physical datatypes
have fixed lengths which cannot be changed.
You used a phystype -char, varchar, binary, or varbinary-that
requires a length. For example, "char(10)" is acceptable,
but char is not.
PERMISSIONS:
Execute permission to public.
TABLES USED:
systypes
sp_addtype Version 4.0 -- 1/15/89 6
______________________________________________________________________
SEE ALSO:
sp_bindefault, sp_bindrule, sp_droptype, sp_rename,
sp_unbindefault, sp_unbindrule, CREATE DEFAULT, CREATE RULE,
CREATE TABLE