Userdefinedfunctions

User defined functions are 3 types. 1.SCALAR FUNCTION 2.INLINE TABLE VALUED FUNCTION 3.MULTI SATATEMENT TABLE VALUED FUNCTIONS

SCALAR FUNCTION :

Scalar functions may or may not have parameters ,but always returns a single value ,the returned value can be any data type .

create function function_name(
@parameter1,@parameter2,.....
)
returns datatype
as
begin

      return return_datattype
end

to execute :
select dbo.functionname(paremeters)

ex:

create function fn_getEnameByJob(
    @job varchar(20)
)
return varchar(50)

return (select ename from emp where job =@job)
end

---select dbo.fn_getEnameByJob('salesman')

INLINE TABLE VALUED FUNCTION :

It returns table it means single select query result only. there is no begin and end end block.

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
     @p1 datatype, 
     @p2 datatype
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

ex:

CREATE FUNCTION fn_getemployeebyjob 
(   
    @job vachar(50)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT empno,ename,hiredate from emp where job=@job
)
GO

MULTI SATATEMENT TABLE VALUED FUNCTIONS :

Multi satatement table valued functions are very similar to inline table valued functions

1.Inline table valued function can not return return table with structure where as Multi satatement table valued functions can return table variable with structure

2.There is no begin and end block in Inline table valued function where as Multi satatement table valued functions have begin and end block

3.After creation of both inline and multi statement table valued functions, then those functions acts as tables ,we can update the data in table (inline table valued function) but we can not update the data in table (multi satetement table function).

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
@table  TABLE 
(
    -- Add the column definitions for the TABLE variable here
    column1 datatype,
    column2 datatype
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END

ex: ``` CREATE FUNCTION fn_getemployeebyjobs ( @job varchar(50) ) RETURNS @table TABLE ( -- Add the column definitions for the TABLE variable here , ) AS BEGIN -- Fill the table variable with the rows for your result set

RETURN

END