Type of Function

When I am thinking about function, I find several article related to UDF factions in SQL server. There are so many type noted in different article that hardly to remember them.

 In my point of view the, UDF function is broadly categorized at 2 types

1.    Scalar Function

2.    Table Function

I provide you only the simple definition of it, not any complicated boring concepts.

Scalar Function:

It returns only a single value.  This value can contains any SQL server data types. The function can accepts multiple parameters with different data types but only return a single value.

This simple example takes 2 decimal values and returns the summation of it. It returns type is also decimal value.

CREATE FUNCTION myFunction

(

            -- Add the parameters for the function here

            @p_Param1 decimal,  ß Input Parameters

            @p_Param2 decimal

)

RETURNS decimal ß Returen Type

AS

BEGIN

            -- Declare the return variable here

            DECLARE @p_ReturnVal decimal

 

            -- Add the T-SQL statements to compute the return value here

            SELECT @p_ReturnVal = @p_Param1 + @p_Param2

 

            -- Return the result of the function

            RETURN @p_ReturnVal ß Return Value

 

END

GO

 

SELECT dbo.myFunction(2,2) Result

 

GO

Table Function

It returns a table data type and it can be used like a view or table

CREATE FUNCTION myTableFunc

(          

            @p_CustName Varchar(50) ß Input Parameters

)

RETURNS TABLE ß Returen Type

AS

RETURN

(

            -- Add the SELECT statement with parameter references here

            SELECT *

            FROM   ledger_descr WHERE descr LIKE @p_CustName+'%'

)

GO

 

SELECT *

FROM   dbo.myTableFunc('Raja')

 

GO

 

Posted by: MR. JOYDEEP DAS