Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_addtype

  No additional text.


Syntax
create procedure sys.sp_addtype
	@typename sysname,			-- name of user-defined type
	@phystype sysname,			-- physical system type of user-defined type
	@nulltype varchar(8) = null,-- nullability of new type
	@owner sysname = null		-- Owner of type (ignored)
as
	set nocount on

	declare @isnull bit			-- default is getansinull()
	declare @systemtype sysname
	declare @numqual1 sysname
	declare @numqual2 sysname 			
	
	select @nulltype = lower(rtrim(@nulltype))
	select @phystype = lower(rtrim(@phystype collate Latin1_General_CI_AS))
	select @systemtype = @phystype
	
	declare @stmt nvarchar(1280)

	-- Warning: User types created via sp_addtype are contained in dbo schema. The @owner parameter is ignored.
	if (@owner is null)
		select @owner = user_name()
	if (@owner <> N'dbo')
		raiserror(15166, 10, 1)

	-- sp_add/droptype only deal with types owned by dbo. So you need to be
	-- db_owner, db_ddladmin or sa (covered by db_owner check) to be able
	-- to create a type.
	if is_member('db_owner')=0 and is_member('db_ddladmin')=0
	begin
		raiserror(15247, -1, -1)
		return (1)
	end

	-- Check null status of the user type
	-- types based on bit not-null by dflt for bckwrd-compat
	if @systemtype = 'bit' and @nulltype is null
					-- If user didn't specify nullability,
					-- make sure it doesn't get set to nullable
					-- by getansinull()
			select @nulltype = 'not null'

	--  Should the user type allow null?
	select @isnull = (case
		when @nulltype is null then getansinull()
		when @nulltype = 'null' then 1
		when @nulltype in ('not null','nonull') then 0
		end)
	if @isnull is null
	begin
		raiserror(15085,-1,-1)
		return (1)
	end


	-- Check that the physical type string matches expected pattern.
	-- It should be one of , (number), or (number, number)
	-- where  when normalized into a single name should be found to be a system type.
	-- Breakdown the @phystype into '@systemtype (@numqual1, @numqual2)'
	if @systemtype like '_%(_%,_%)%'
	begin		
		-- There should be no trailing string after the ')'
		if (len(@systemtype) - charindex(')',@systemtype)) > 0
		begin
			raiserror(15036,-1,-1,@phystype)
			return (1)
		end	

		-- santity check the offsets before parsing
		if 	(charindex(',',@systemtype) < charindex('(',@systemtype)) or
			(charindex(',',@systemtype) > charindex(')',@systemtype))
		begin
			raiserror(15036,-1,-1,@phystype)
			return (1)		
		end
		
		select @numqual1 = substring(@systemtype,
			charindex('(',@systemtype) + 1,
			charindex(',',@systemtype) - 1 - charindex('(',@systemtype))
		
		select @numqual2 = substring(@systemtype,
			charindex(',',@systemtype) + 1,
			charindex(')',@systemtype) - 1 - charindex(',',@systemtype))
			
		-- Extract typename
		if @systemtype is not null
			select @systemtype = substring(@systemtype, 1, charindex('(', @systemtype) - 1)
	end
	else if @systemtype like '_%(_%)%'
	begin
		-- There should be no trailing string after the ')'
		if (len(@systemtype) - charindex(')',@systemtype)) > 0
		begin
			raiserror(15036,-1,-1,@phystype)
			return (1)
		end		

		-- santity check the offsets before parsing
		if 	(charindex(')',@systemtype) < charindex('(',@systemtype))
		begin
			raiserror(15036,-1,-1,@phystype)
			return (1)		
		end
		
		select @numqual1 = substring(@systemtype,
			charindex('(',@systemtype) + 1,
			charindex(')',@systemtype) - 1 - charindex('(',@systemtype))
			
		-- Extract typename
		if @systemtype is not null
			select @systemtype = substring(@systemtype, 1, charindex('(', @systemtype) - 1)
	end

	-- Normalize typename.
	select @systemtype = rtrim(@systemtype)
	select @systemtype= (case @systemtype
		when 'character' then 'char'
		when 'character varying' then 'varchar'
		when 'char varying' then 'varchar'
		when 'integer' then 'int'
		when 'dec' then 'decimal'
		when 'binary varying' then 'varbinary'
		when 'national character varying' then 'nvarchar'
		when 'national char varying' then 'nvarchar'
		when 'national character' then 'nchar'
		when 'national char' then 'nchar'
		when 'ncharacter varying' then 'nvarchar'
		when 'ncharacter' then 'nchar'
		when 'nchar varying' then 'nvarchar'
		when 'national text' then 'ntext'
		when 'rowversion' then 'timestamp'
		when 'double precision' then 'float'
		else @systemtype
		end)

	-- disallow new varchar(max) types through sp_addtype
	if @systemtype in ('varchar', 'nvarchar', 'varbinary') and @numqual1 = 'max'
	begin	
		raiserror(15108,-1,-1)
		return (1)
	end

	-- disallow xml (cannot create thru CREATE TYPE either)
	if @systemtype = 'xml'
	begin
		raiserror(15656,-1,-1)
		return (1)
	end

	-- check that type qualifier(s) are numeric.
	if (@numqual1 is not null and isnumeric(@numqual1)=0) or
	   (@numqual2 is not null and isnumeric(@numqual2)=0)
	begin
		raiserror(15036,-1,-1,@phystype)
		return (1)
	end
	
	--  Check that physhical type exists. System physical types have
	--  a xusertype < 256 and are owned by sys.
	if not exists (select * from sys.types where user_type_id < 256
		and (name collate Latin1_General_CI_AS)= @systemtype and schema_id = 4)
	begin
		raiserror(15036,-1,-1,@phystype)
		return (1)
	end

	-- Reconstruct the physical type name from the validated parts
	-- in preparation to call the CREATE TYPE statement.
	select @phystype = @systemtype
	if (@numqual1 is not null and @numqual2 is not null)
		select @phystype = @phystype + '(' + RTRIM(@numqual1) + ',' + RTRIM(@numqual2) + ')'
	else if (@numqual1 is not null)
		select @phystype = @phystype + '(' + RTRIM(@numqual1) + ')'

	-- Construct create type stmt
	select @stmt = 'create type [dbo].' +  quotename(@typename)
		+ ' from ' + @phystype + ' '
		+ case @isnull when 1 then 'null' else 'not null' end

	-- Create user defined type
	EXEC(@stmt)

	return (@@error) -- sp_addtype

 
Last revision 2008RTM
See also

  sp_bindrule (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash