7

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
   )
)
Jason
  • 349
  • 3
  • 9
  • [This page](http://www.techrepublic.com/blog/the-enterprise-cloud/understand-when-to-use-user-defined-functions-in-sql-server/) specifically says "You can use scalar-valued UDFs as the default value for a column in a table." The [MSDN documentation](https://technet.microsoft.com/en-us/library/ms191320(v=sql.110).aspx) doesn't say anything about not being able to do it. – Jason May 22 '15 at 17:01
  • [This forum thread](http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165697) 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](http://stackoverflow.com/questions/1744455/using-udf-for-default-value-of-a-column) talks about using a parameterized UDF as the default constraint, but the "answer" talks about not using a UDF at all. – Jason May 22 '15 at 17:01

1 Answers1

7

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
   )
)
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75