Creating and Using User-Defined Types : Create Type « Data Types « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Data Types
3. Create Type

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>