How to write a Function in SQL Server

This code shows how to write a function in sql server
A function which takes Empno and calculate Total and Anuual Salary and returns them


Create Function get_EmpData(@empno int)
Returns @MyTable Table(Total Money,Annual Money)
As
Begin
Declare @Sal Money,@Comm Money
Declare @TSal Money,@ASal Money
Select @Sal=sal, @Comm=comm from emp
where empno@empno

Set @TSal = @Sal + IsNull(@Comm,0)
Set @ASal = (@Sal + IsNull(@Comm,0))*12
Inset into @MyTable Values(@TSal,@ASal)
Return

End

Calling the above function

Select * from get_EmpData(1005)

Regards
S.S.Bajoria


Comments

Author: umang29 Sep 2008 Member Level: Bronze   Points : 0

This coding is very useful.by this way a programmer get much a help.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: