Table Valued Functions in SQL Server  :-

  • These functions returns set of records  i:e table.
  • The return type of this functions must be “table”.
  • The return expression is  ”select statement”.

Syntax :-

               Create/alter function <name> (parameters)

                  Returns table

                   As

             Return(select statement).

Example  :-

           Create function getemp(@d  int)

           Returns table

                As

           Return(select * from emp where deptno=@d)

How to execute the function:

{The table valued functions are called in from clause of select statement}

Select * from dbo.getemp(10)

 

{i:e  it executes the all employees worked in 10th department}

Drop procedure in SQL Server  :-

      Drop procedure <name>

Drop function in SQL Server :-

           Drop function<name>