User Defined Function

In every sql statement you made, sometime there's sql statement that you use over and over. In a big project scale, there's a good idea to put it into Store Procedure or Function, depends on your needs. This time, i will discuss about Function,use to be konwn as a User-Defined Functions ( UDFs ).

User-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. UDFs divide into 3 types, which are Scalar Functions, Inline table-valued and multiline table-valued

1. Scalar Functions
Syntax:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
(Source : SQL Help Documentation)

sample :

create function dbo.udf_hitung_score
(
@nilai numeric
)returns char(11)
as
begin
declare @grade char(1)

set @grade = case when @nilai between 85 and 100 then 'A'
when @nilai between 70 and 84 then 'B'
when @nilai between 60 and 69 then 'C'
when @nilai between 50 and 59 then 'D'
else 'F' end
return(@grade)
end

Example :

select dbo.udf_hitung_score(82)

You can use it as follow :
example, you have Table A with attribut Mark contain Student Mark with Numeric data type, than your query may become like this :

select nama_mahasiswa, nama_matakuliah, kelas , grade = dbo.udf_hitung_score(nilai)
from A
order by kelas

2. Inline Table-Valued Functions
syntax:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS TABLE [ WITH [ ,...n ] ] [ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_Master_customers
(
@customerid nchar(10)
) RETURNS TABLE
as
RETURN (select * from customers
where customerid = @customerid )

example :

select * from dbo.udf_Master_customers('Alfki')

you can join it to with others table.

3. Multistatement Table-valued Functions
Syntax :
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE <> [ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_order
(
@customerid nchar(10)
) RETURNS @order TABLE (contactname varchar(60) ,orderid int, orderdate datetime
, requireddate datetime )
as
begin
declare @contactname varchar(60)

select @contactname = contactname
from customers where customerid = @customerid

insert @order
select @contactname , orderid, orderdate, requireddate
from orders
where customerid = @customerid

RETURN
end

example

select * from dbo.udf_order('alfki')

multistatement table-valued used if you need more logical programming to manipulate the data before displaying it.

Each type of UDFs has their own unique function depends on your needs in your project. But surely, it's very helpfull in your project later.

After this, i will try to collect some logic and make it as a function that may usefull for you.

Have a try and good luck.