We can use these user defined functions to return some values by computing the data in the database. but we cannot change the database using any stored function.
These functions must return a value,only have input parameters,should be compiled and executed each time when we want to call the function and can use inside the stored procedures as well.
We’ll look at the format of a Stored Function.
CREATE FUNCTION functionName ( @para1 varchar(50),@para2 varchar(50) ) AS BEGIN RETURN (SELECT @para1 + ' '+ @para2) FROM emp; END;
It doesn’t have many changes of the format with compared to the Stored Procedures.I hope you got it.
So i’ll show some examples how we can use these functions for our systems.
- A salary calculator
CREATE FUNCTION calc_salary (@id varchar) RETURNS float AS BEGIN declare @hrs_worked int declare @rate float declare @amt float SELECT @rate = e.rate, @hrs_worked = e.hrs_worked FROM emp e WHERE e.id = @id SET @amt = @rate * @hrs_worked RETURN @amt end;
You can do any mathematical operations or whatever the things you want to the data in the database and return as you can see in the above example.Not a big deal 🙂
So you can call the written function like this.
</pre> <div class="page" title="Page 5"> <div class="section"> <div class="layoutArea"> <div class="column"> declare @salary float exec @salary = calc_salary 'e001' print @salary </div> </div> </div> </div> <pre>
So that’s it basically about stored functions.Please let me know if you any queries regarding any of these 🙂