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:
——–
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.