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.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |