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.