Question

我有惯例,我正试图利用这一职能设立一栏。

它给我留下错误。

表格“可使用”中的组合式SSN无法维持,因为这个栏目不是决定性的。

职能如下:

ALTER FUNCTION [dbo].[FormatSSN]()
RETURNS VARCHAR(11)
AS
BEGIN
    return   ;
END

下面是用这一功能添加一栏的问题:

ALTER TABLE SomeTable
ADD FormattedSSN as dbo.FormatSSN() PERSISTED

请说明是否有任何办法。感谢。

Answer 1

∗∗∗∗∗∗

ALTER FUNCTION [dbo].[FormatSSN]
(
@SSN    VARCHAR(9)
)
RETURNS CHAR(11)
WITH SCHEMABINDING
AS
BEGIN
  your stuff here
END

之后进行核实:

IF OBJECTPROPERTY (OBJECT_ID(N [dbo].[FormatSSN] ), IsDeterministic ) = 1
   PRINT  Function is detrministic. 
ELSE IF OBJECTPROPERTY (OBJECT_ID(N [dbo].[FormatSSN] ), IsDeterministic ) = 0
   PRINT  Function is NOT detrministic 
GO

页: 1

Answer 2

2. 如何直接确定定义:

ALTER TABLE SomeTable
ADD FormattedSSN as
    case when len(EmployeeSSN) = 9 then
            substring(EmployeeSSN, 1, 3) +  -  +
            substring(EmployeeSSN, 4, 2) +  -  +
            substring(EmployeeSSN, 6, 4)
    else EmployeeSSN end
PERSISTED

Answer 3

不是说UDF,而是把计算出来的一栏表示为

Case When Len(EmployeeSSN) = 0 Then 
      SUBSTRING(EmployeeSSN, 1, 3) +  -  + 
      SUBSTRING(EmployeeSSN, 4, 2) +  -  + 
      SUBSTRING(EmployeeSSN, 6, 4)
    Else EmployeeSSN End

在制作表格时,请加一栏:

[NewColumnName]  As
   (Case When len([UpdateUserId])=(0) T
         Then (((substring([UpdateUserId],(1),(3))+ - )+
                 substring([UpdateUserId],(4),(2)))+ - )+
                 substring([UpdateUserId],(6),(4)) 
         Else [UpdateUserId] End) PERSISTED,

Answer 4

创建适当数据类型的非投入一栏。创建“插入触发点”(如果数据发生变化,则更新触发点)。然后,你可以把职能产出放在一栏中。

ALTER TABLE SomeTable ADD FormattedSSN VARCHAR(11)
GO

CREATE TRIGGER dbo.TR_I_SomeTable ON  dbo.SomeTable AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    update s
    set s.FormattedSSN = dbo.FormatSSN()
    from SomeTable AS s
        join inserted i on i.id = s.id

END
GO

友情链接