Use a UDF as the default value in a table column in SQL Server

Question

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.

Here's my 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 ) )

This page specifically says "You can use scalar-valued UDFs as the default value for a column in a table." The MSDN documentation doesn't say anything about not being able to do it. — Jason
– Jason, Commented May 22, 2015 at 17:01
This forum thread gives example syntax, treating it like a computed column instead of a default constraint, but that doesn't work for me either. And this StackExchange question talks about using a parameterized UDF as the default constraint, but the "answer" talks about not using a UDF at all. — Jason
– Jason, Commented May 22, 2015 at 17:01

Giorgi Nakeuri · Accepted Answer · 2015-05-22 17:07:26Z

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 ) )

Marked as solved. I just wish I could give you more kudos than that for a quick and correct answer. Thanks!
you mean using dbo.sCurrentAppUser() instead of sCurrentAppUser(), nice..

Collectives™ on Stack Overflow

Use a UDF as the default value in a table column in SQL Server

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related