在SQL Server的表格列中使用UDF作为默认值 [英] Use a UDF as the default value in a table column in SQL Server
问题描述
我在SQL Server 2012 Express中创建了一个缩放器UDF(称为sCurrentAppUser()),我想在定义表时将此UDF用作默认值。但是每次尝试时,我都会收到'sCurrentAppUser'不是公认的内置函数名称的错误。
I created a scaler UDF (called sCurrentAppUser()) in SQL Server 2012 Express and I would like to use this UDF as a default value when defining a table. But every time I try, I get an error of "'sCurrentAppUser' is not a recognized built-in function name."
因为我不能发布两个以上的函数链接(信誉),我将在评论中链接到我的研究和参考。
Since I can't post more than two links yet (reputation), I'll link to my research and references in a comment.
这是我的UDF:
ALTER FUNCTION [dbo].[sCurrentAppUser] ()
RETURNS nVarChar(128)
AS BEGIN
DECLARE @CurrentAppUser nVarChar(128)
IF EXISTS (SELECT 1 FROM ActiveConnections WHERE ComputerName = host_name ()) BEGIN
SELECT @CurrentAppUser = CONVERT (nVarChar(128), LoginUser)
FROM ActiveConnections
WHERE ComputerName = host_name ()
END ELSE BEGIN
SELECT @CurrentAppUser = Convert (nVarChar(128), suser_sname ())
WHERE NOT EXISTS (
SELECT 1
FROM ActiveConnections
WHERE ComputerName = host_name ()
)
END
RETURN @CurrentAppUser
END
而我尝试在第一列上使用默认约束创建表:
And my attempt at creating the table with the default constraint on the first column:
CREATE TABLE [dbo].[Clients](
[ModifyingUser] [nvarchar](128) NOT NULL DEFAULT sCurrentAppUser (),
[Modification] [char](1) NULL DEFAULT 'A',
[ModifyingHost] [nvarchar](128) NOT NULL DEFAULT host_name (),
[ClientID] [uniqueidentifier] NOT NULL,
[Label] [nvarchar](1024) NULL,
CONSTRAINT [PK_Clients] PRIMARY KEY (
[ClientID] ASC
)
)
推荐答案
您应该为该函数添加架构名称:
You should add schema name for that function:
CREATE TABLE [dbo].[Clients](
[ModifyingUser] [nvarchar](128) NOT NULL DEFAULT dbo.sCurrentAppUser (),
[Modification] [char](1) NULL DEFAULT 'A',
[ModifyingHost] [nvarchar](128) NOT NULL DEFAULT host_name (),
[ClientID] [uniqueidentifier] NOT NULL,
[Label] [nvarchar](1024) NULL,
CONSTRAINT [PK_Clients] PRIMARY KEY (
[ClientID] ASC
)
)
这篇关于在SQL Server的表格列中使用UDF作为默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!