Syntax for Default Value for User Defined Type as Table

Question

Let say you have a User Defined Type that follows:

CREATE TYPE [dbo].[MyDefineType] As Table                
(
ID int NOT NULL
, Column1 int NOT NULL   
, Column2 Nvarchar(128)  NULL
, Column3 Nvarchar(128) NULL
, Column4 Nvarchar(128) NULL 
, Column5 Nvarchar(128) NULL       
)

So when I passed a valued table through a stored procedure, the type mirrors the schema of the table.

My question is what is the syntax to give one of the columns a defualt value? For example, if I passed the these values through a stored procedure in a loop, I could give column 5 a default value, so if I passed values for only the first 4 columns, 5 would defalut to what I defined it to be if no value was passed. Is that possible with a user defined type?

Answer 1

Use the normal syntax (see CREATE TYPE, MSDN):

(As noted in the comments to my answer the proper normal syntax would be to use named constraints, but table types can't use named constraint and have to use the "shorthand" syntax).

CREATE TYPE [dbo].[MyDefineType] As Table                
(
  ID int NOT NULL DEFAULT 0
, Column1 int NOT NULL DEFAULT 99
, Column2 Nvarchar(128) NULL DEFAULT N'DefaultValue'
, Column3 Nvarchar(128) NULL
, Column4 Nvarchar(128) NULL 
, Column5 Nvarchar(128) NULL       
)

For example, using the above definition with defaults on the first three columns:

DECLARE @t MyDefineType
INSERT @t VALUES (1, DEFAULT, DEFAULT, N'c', N'd', N'e')
SELECT * FROM @t

ID  Column1 Column2         Column3 Column4 Column5
1   99      DefaultValue    c       d       e