Function Types in SQL Server – SQL Server Tutorial

Function Types in SQL Server   :-

It can be divided into two types

  1. Scalar valued
  2. Table valued

Scalar Valued function in SQL Server:-

These functions returns only one value of type int,varchar date time etc.

Syntax  :–

                 Create/alter function <name>(parameters)

                     Returns <type>

                          As

                       Begin

                       Statements

                    Return<exper>

                         End.

Example  :-

            Create function addnum(@x int,@y int)

                  Returns  int

                      As

                    Begin

                   Declare @z int

                Set @z=@x+@y

                   Return @ z.

                  End.

Executing the function using select statement  :

Select dbo.addnum(10,20)

 Executing the function from another tsql block :

               Declare @ a  int,@b int,@c int

                    Set @a=30

                     Set @b=50

                       Set @c=dbo.addnum(@a,@b)

                   Print @c

Program  :

Create a function to calculate employee experience?

                    Create function expr( @e int)

                       Returns int

                            As

                         Begin

                       Declare  @doj datetime,@x int

                  Select      @doj=hiredate  from    emp

                                             Where empno=@e

                        Set @x =abs(datediff(yy,getdate(),@doj))

                        Return @x

                           End

                                  ↓

                           Select dbo.expr(7566)

                                   ↓

Write a program to print all employee names and their experience?

                                  ↓

Select ename,dbo.expr (empno) from exp.

Write a program to calculate the employee experience,if experience≤30 then increment the emp salary by 10% otherwise delete the record from table :-

                   Declare c1 cursor for

                        Select empno from emp                           →hiredate

                 Declare @eno int,@x int                                →@doj date time

                     Open c1

                Fetch next from c1 into @eno                                  →@doj

                While(@@fetch_status=0)

                     Begin

                      Set @x=dbo.expr(@eno)

                        If @x<=30

                    Update emp set sal=sal*1.1

                             Where emp no=@eno

                              Else

                          Delete from emp where empno=@eno

                       Fetch next from  c1 into @eno

                               End

                         Close c1.

Example  :-

                    Create function expr(@doj datetime)

                    Returns int

                     As

                    Begin

                     Declare @x int

               Set @x=abs[datediff(yy,getdate(),@doj))

                  Return @x

                   End

Acct_mast:

          Accno    accname     bal

Acc_trans:-

Trid       ttype tdate         tamt         accno.

procedure for new account  :-

Create  procedure    new_acct(@ a   int,@n  varchar(20),@b  money)

                        As

                       Begin

              Insert into acct_mast values(@a,@n,@b)

                 End.

                    ↓

Exec   new_acct 1,’A’,5000.

Procedure for {closing} close_acct :-

              Create procedure close_acct(@a int)

                    As

                   Begin

                Delete from   acct_mast where accno=@a

                   End.

Procedure for credit  :-

            Create procedure credit(@a int,@amt money)

               As

              Begin

             Update acct_mast set bal=bal+@amt

                             Where accno=@a.

              Insert into acct_trans(ttype,tdate,tamt,acno)

                                      Values (‘D’ ,getdate(),@amt,@a)

                       End.

Procedure for Debit  :-

  Create procedure debit(@a  int,@amt money)

   As

  Begin

Update acct_mast set bal=bal-@amt

                    Where accno=@a

  Insert into acct_trans(ttype,tdate,tamt,accno)

           Values(‘w’,getdate ( ),@amt,@a)

  End.

Procedure for money transfer :-

          Create  procedure transfer(@s  int,@t int,@amt money)

            As

     Begin

     Update acct-mast set bal=bal-@amt  where accno=@s

      Update acct-mast  set bal =bal+@amt  where accno=@t.

      Insert into acct_trans(ttype,tdate,tamt,accno)values 

                                      (‘w’,getdate ( ),@amt,@s).

         Insert into acct-trans(ttype,tdate,tamt,accno)

                   Values(‘D’,getdate ( ),@amt,@t).

                End.

@@Rowcount  :-

It returns no. of records effected by the sql statement.

         →update

                @c1=@@rowcount

                  Update

                @c2=@@rowcount

                    Insert

                    Insert

                If @c1=@c2

               Commit

                 Else

                  Rollback.

Procedure for balance  :

          Create function balance(@a  int)

              Returns money

            As

         Begin

      Declare @bal money

    Select  @bal =bal from acct-mast

                      Where accno=@a

         Return @bal

          End.

Related Projects

Leave a Reply

Your email address will not be published. Required fields are marked *