Function Types in SQL Server :-
It can be divided into two types
- Scalar valued
- 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.