SQL Server/T-SQL Tutorial/Data Types/Create Type

Материал из SQL эксперт
Перейти к: навигация, поиск

Creating and Using User-Defined Types

   <source lang="sql">

4> 5> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 6> GO 1> 2> CREATE TABLE dbo.MyCount 3> (ID int NOT NULL, 4> AccountNBR AccountNBR) 5> GO 1> 2> CREATE PROCEDURE dbo.usp_SEL_CustomerAccount @CustomerAccountNBR AccountNBR 3> AS 4> SELECT ID, AccountNBR 5> FROM dbo.MyCount 6> WHERE AccountNBR = @CustomerAccountNBR 7> GO 1> 2> DECLARE @AccountNBR AccountNBR 3> SET @AccountNBR = "1294839482" 4> EXEC dbo.usp_SEL_CustomerAccount @AccountNBR 5> GO ID AccountNBR


--------------

1> 2> EXEC sp_help "dbo.AccountNBR" 3> GO Type_name

        Storage_type
                 Length Prec        Scale       Nullable                            Default_name
                                                                                             Rule_name
                                                                                                      Collation


---------------------------------------------------------------------------------------------------------------
------ ----------- ----------- ----------------------------------- -----------------------------------
--------------------------
-----------------

AccountNBR

        char
                     14          14        NULL no                                  none
                                                                                             none
                                                                                                      Chinese_PRC_CI_AS

1> 2> drop table dbo.MyCount 3> GO 1> drop procedure dbo.usp_SEL_CustomerAccount 2> GO 1> drop type dbo.AccountNBR 2> GO 1></source>


Dropping User-Defined Types

   <source lang="sql">

4> 5> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 6> GO 1> 2> DROP TYPE dbo.AccountNBR 3> GO 1></source>


Identifying Columns and Parameters that Use User-Defined Types

   <source lang="sql">

4> 5> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 6> GO 1> 2> CREATE TABLE dbo.MyCount 3> (ID int NOT NULL, 4> AccountNBR AccountNBR) 5> GO 1> 2> SELECT OBJECT_NAME(c.object_id) Table_Name, c.name Column_Name 3> FROM sys.columns c 4> INNER JOIN sys.types t ON 5> c.user_type_id = t.user_type_id 6> WHERE t.name = "AccountNBR" 7> GO Table_Name

        Column_Name


---------------------------------------------------------------------------------------------------------------

MyCount

        AccountNBR

1> 2> 3> drop table dbo.MyCount 4> GO 1> 2> drop type dbo.AccountNBR 3> GO</source>


Now see what parameters reference the AccountNBR data type

   <source lang="sql">

3> 4> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 5> GO 1> 2> 3> SELECT OBJECT_NAME(p.object_id) Table_Name, p.name Parameter_Name 4> FROM sys.parameters p 5> INNER JOIN sys.types t ON 6> p.user_type_id = t.user_type_id 7> WHERE t.name = "AccountNBR" 8> GO Table_Name

        Parameter_Name


---------------------------------------------------------------------------------------------------------------

1> 2> drop type dbo.AccountNBR 3> GO 1></source>


The syntax of the CREATE TYPE statement is as follows:

   <source lang="sql">

CREATE TYPE [type_schema_name.] type_name

     {[FROM base_type [(precision [,scale])] [NULL|NOT NULL]]
      |[EXTERNAL NAME assembly_name [.class_name]]}

10> CREATE TYPE zip 11> FROM CHAR(5) NOT NULL 12> GO 1> 2> drop type zip; 3> GO</source>