An Introduction to SQL Server Scalar UDFs

As you can see here, the UPDATE Statements cannot be used in a scalar UDF.

CREATE FUNCTION fnDeleteTest

(

             @EmailId varchar(255)

)

RETURNS int

AS

BEGIN

             DELETE FROM  [dbo.FunctionTest]

             WHERE [FT_EMail] = @EmailId

             RETURN 1

END

Output:

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

Invalid use of 'DELETE' within a function.

And last of all, the DELETE statement cannot be used in a scalar UDF, as you can see here.

A scalar function's output can be used in the SELECT statements of T-SQL, something that you cannot with a stored procedure. For example:

CREATE FUNCTION fngetEmail

(

       @FName varchar(50),

       @LName varchar(50)

)

RETURNS varchar(255)

AS

BEGIN

       DECLARE @EmailId varchar(255)

       SELECT @EmailId = [FT_EMail]

           FROM [FunctionTest]

       WHERE [FT_FName] = @FName AND [FT_LName] = @LName

       RETURN @EmailId

END

SELECT  dbo.fngetEmail([FT_FName],[FT_LName]) as EMAIL FROM FunctionTest

Output:

Email

--------

navneeth_naik@yahoo.com

anil_bahirat@yahoo.com

amol_kulkarni@yahoo.com

murthy@yahoo.com

Note in the above SELECT statement that the function is directly used in the SELECT statement.