用户定义类型作为表的默认值的语法 [英] Syntax for Default Value for User Defined Type as Table

问题描述

假设您有一个用户定义的类型，如下所示:

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.

我的问题是为其中一列提供默认值的语法是什么?例如，如果我通过循环中的存储过程传递这些值，我可以给第 5 列一个默认值，所以如果我只传递前 4 列的值，如果没有，5 将默认为我定义的值值被传递.用户定义的类型可以实现吗?

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?

推荐答案

使用正常语法(请参阅 创建类型，MSDN):

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

这篇关于用户定义类型作为表的默认值的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！