[SQL/study] UDF (user-defined function)

What is UDF?

The function helps users not to write the same query again. It means recycling. There are two types of Functions. 1. Scalar Function    - return the one output. 2. Table-Valued Function    - return various outputs in a table.

---

Basic Form

create function fufuncname (@prm1 datatype1, @pmr2 datatype..)
returns datatype
as
begin
	return ReturnValue
end

 

EX1. function of adding two number

CREATE FUNCTION AddNumbers(@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    RETURN @num1 + @num2;
END

 

EX2. function of combining two strings

CREATE FUNCTION ConcatStrings(@str1 NVARCHAR(50), @str2 NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN @str1 + ' ' + @str2;
END

select dbo.AddNumbers(10, 20);
--30 will be return
select dbo.ConcatStrings('Hello', 'World');
--"Hello world" will be return

 

Application Form

CREATE FUNCTION function_name (@par datatype(size), @par datatype(size))
RETURNS datatype
AS
BEGIN
    -- function logic
    RETURN value;
END

 

EX. using DECIMAL for calculate the price 

CREATE FUNCTION fnCalculateTotal(@price DECIMAL(10, 2), @taxRate DECIMAL(5, 3))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @price + (@price * @taxRate);
END;

--@par1 DECIMAL(5, 2) >> 소수점 포함, 총 5자리 중 소수점 이하 2자리
--@par2 DECIMAL(6, 3) >> 소수점 포함, 총 6자리 중 소수점 이하 3자리

create or alter function funGetFullName (@Cusid int)
returns varchar(50)
as
begin
    declare @fullname varchar(50)
    select @fullname = concat_ws(' ', P.FirstName, P.LastName)
    from Sales.Customer C
    join Person P
        on C.PersonID = P.BusinessEntityID
    where C.CustomerID = @Custid
    return @fullname
end

a) declare @fullname varchar(50)

   - declare a local variable

b) select @fullname = concat_ws(' ', P.FirstName, P.LastName)

   - connect FirstName and LastName with space.

c) where C.CustomerID = @Custid

   - find a specific CustomerID that was inputted