Stored Functions

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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s