Stored Procedure

Create user-defined datatype using sp_addtype Stored procedure in SQL Server

Hi friends, today in SQL Server System Stored procedure you will learn how to create user-defined datatype using stored procedure sp_addtype.

Sp_addtype is used to provide an alias to datatype. You can find the find the information of the newly created user-defined datatype in sys.types table for a specific database. To get enable user-defined datatype in all new user-defined databases you need to create that datatype in model database. User-defined data types cannot be defined for timestamp, table, xml, varchar(max), nvarchar(max), varbinary(max).

Syntax of sp_addtype is:

sp_addtype [ @typename = ] type,

    [ @phystype = ] system_data_type

    [ , [ @nulltype = ] ‘null_type’ ] ;

Arguments

@typename is name of new user-defined datatype.

@phstype is system datatype of SQL Server

@nulltype is to specify whether NULL value is allowed for this datatype or not.

Let’s create an alias datatype for datetime datatype.

EXEC sp_addtype ‘Birthdate’,Datetime,’NOT NULL’

Here we have created Birthdate alias for Datetime datatype.

We can check our newly created alias in system table sys.types.

sp_addtype

Now let’s create a table used this user defined datatype ‘Birthdate’

Create table Child

(

ChildName varchar(20),

DOB birthdate

)

So friends today we have learned how to create user-defined datatype. I will continue with other system stored procedures in my next blog.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s