User defined functions

User defined functions are  kind of procedures/routines  that performs actions or calculations and return back the results either as scalar or table value (result set)

They are categorized as two types

• Scalar valued Functions : Are the functions which returns single data value, as declare in the returns clause, these are functions are used in select clause
  Example:
  Create function dbo.test_scalarFn (@I int)
  Returns  int
  AS
  BEGIN
  declare @j int
  select @j = @i *100
  Return @j
  END
  
  
  select dbo.test_scalarFn(BusinessEntityID) as BusinessEntity_multi_by100, FirstName
  

  from dbo.person

  
  

  Result set 

  BusinessEntity_multi_by100 FirstName
  100                                          Sam
  200                                          David
  10000                                      Scott
• Table-Valued Functions : Are functions which returns table data type as result, as declare in the returns clause, these functions are used in from clause               

          Example:

          Create function dbo.test_TableFn (@I int)
          Returns  table
          AS
          Return
          select FirstName , LastName from person.person
          where BusinessEntityID = @I
          END
     
          select * from dbo.test_TableFn(100)
            Result set     
         FirstName  LastName
            Scott           Tiger