The syntax of the default value for a user-defined table type

Question

Let's say you have a user-defined type:

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 the valuable table through a stored procedure, the type reflects the table schema.

My question is, what is the syntax to give one of the columns a defualt value? For example, if I passed these values ​​through a stored procedure in a loop, I could give column 5 a default value, so if I passed values ​​only to the first 4 columns, I would split 5 into what I determined if there wasn’t transmitted. Is this possible with a user-defined type?

Answer 1

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

(As noted in the comments on my answer, the correct normal syntax would be to use named constraints, but table types cannot use the named constraint and must use the "abbreviated" 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 default values ​​in 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 cde