An Introduction to SQL Server Scalar UDFs

By : Navneeth Diwaker Naik May 17, 2004

Printer friendly

One of the best features of SQL Server 2000 is its user-defined functions (UDFs). UDFs offer many of the benefits of both views and stored procedures, and more.

Unlike views, UDFs can take zero or more input parameters and return a scalar value, or can even return a table. Unlike stored procedures, the output of an UDF can be used in a SELECT statement, which is really beneficial. UDFs are compiled and optimized in the same way as a stored procedure.

SQL SERVER 2000 supports three different types of UDFs. They are scalar, inline table-valued, and multi-statement table-valued functions. In this article, I will be discussing scalar user-defined functions in detail.

Scalar user-defined functions must be deterministic, e.g., they cannot use functions, such as getdate(), whose return value differs with time to time. A scalar function must always return the same value if the input is same.

Take a look at, or execute the following T-SQL:

CREATE FUNCTION fnGetDate()

RETURNS varchar(100)

AS

BEGIN

       DECLARE @test varchar(100)

       SELECT @test = cast(getdate()as varchar(100))

       RETURN @testst

END

Output:

Server: Msg 443, Level 16, State 1, Procedure fnGetDate, Line 6

Invalid use of 'getdate' within a function.

The GetDate() function is not deterministic so we cannot use this in the Scalar UDF. In addition, ccalar functions cannot be used to INSERT/UPDATE/DELETE the data from a table. To demonstrate this, you can view or run the following scripts to create a sample database and tables, and populate sample data in the table.

CREATE DATABASE DemoFunction

USE DemoFunction

CREATE TABLE [FunctionTest]

(

       [FTID] [int] NOT NULL,

       [FT_FName] [varchar](50) NOT NULL,

       [FT_MName] [varchar](50) NULL,

       [FT_LName] [varchar](50) NOT NULL,

       [FT_EMail] [varchar](255) NULL,

             CONSTRAINT [PK_FT] PRIMARY KEY NONCLUSTERED

             (

                     [FTID]

             )       ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT INTO FunctionTest([FTID],[FT_FName],[FT_LName],[FT_EMail])

       SELECT 1,'Navneeth','Naik','navneeth_naik@yahoo.com' UNION

       SELECT 2,'Anil','Bahirat','anil_bahirat@yahoo.com' UNION

       SELECT 3,'Amol','Kulkarni','amol_kulkarni@yahoo.com'

 SELECT 4,'Satya','Murthy','murthy@yahoo.com'

GO

CREATE FUNCTION fnInsertTest

(

       @FName varchar(50),

       @LName varchar(50),

       @MName varchar(50),

       @EmailId varchar(255)

)

RETURNS int

AS

BEGIN

INSERT INTO FunctionTest([FTID],[FT_FName],[FT_LName],[FT_EMail])

SELECT 1,'Navneeth','Naik','navneeth_naik@yahoo.com' UNION

SELECT 2,'Anil','Bahirat','anil_bahirat@yahoo.com' UNION

SELECT 3,'Amol','Kulkarni','amol_kulkarni@yahoo.com'

SELECT 4,'Satya','Murthy','murthy@yahoo.com'            

        RETURN 1

END

Output:

Server: Msg 443, Level 16, State 2, Procedure fnInsert, Line 11

Invalid use of 'INSERT' within a function.

As you can see, the INSERT statement cannot be used in a scalar UDF.

CREATE FUNCTION fnUpdateTest

(

       @FName varchar(50),

       @LName varchar(50),

       @EmailId varchar(255)

)

RETURNS int

AS

BEGIN

       UPDATE [dbo.FunctionTest]

       SET [FT_FName] = @FName, [FT_LName] = @LName

       WHERE [FT_EMail] = @EmailId

       RETURN 1

END

Output:

Server: Msg 443, Level 16, State 2, Procedure fnDMLTest, Line 10

Invalid use of 'UPDATE' within a function.