-----------------
--- Functions ---
-----------------

----------------------
-- Scalar Functions --
----------------------

---CREATING SCALAR FUNCTION---

--1--
CREATE FUNCTION fn_DateFormat 
   (@indate datetime, @separator char(1))
RETURNS NCHAR(20)
AS
BEGIN
   RETURN 
   CONVERT(nvarchar(20), Datepart(DD,@indate))
   + @separator
   + CONVERT(nvarchar(20), Datepart(MM, @indate))
   + @separator
   + CONVERT(nvarchar(20), Datepart(YY, @indate))
END

--Execute (Must Use Schema Before Function Name)
PRINT dbo.fn_DateFormat(GETDATE(), '/');

--Execute On A Table
select birthdate, dbo.fn_DateFormat(birthdate, '/') from Employees;

--2--
CREATE FUNCTION dbo.tax_fun		---	Receives an amount and adds tax
(@num_to_tax MONEY)
RETURNS MONEY
AS
	BEGIN
		RETURN @num_to_tax*1.155
	END


select dbo.tax_fun(10)


---USING SCALAR FUNCTION IN SELECT STATMENT---
SELECT productid, productname, unitprice, dbo.tax_fun(unitprice) AS newprice ----Price and Price inc. VAT
FROM products

-----USING UDF dbo.tax_fun IN DML-----------------

SELECT * INTO copy_tbl_products 
FROM products --- PRODUCTS 

ALTER TABLE copy_tbl_products
ADD total_price MONEY				---Add a column for price inc. VAT 

SELECT * FROM copy_tbl_products

-- Can be used in any SQL statement (SELECT, DML, etc..)
UPDATE copy_tbl_products
SET total_price = dbo.tax_fun(unitprice)	

SELECT * FROM copy_tbl_products

DROP TABLE copy_tbl_products

-----FUNCTION FOR CHECKING IF ORDER EXISTS-----------------------
--boolean Function--

CREATE FUNCTION dbo.ord_ck_fun
(@order_num_parameter INT)
 RETURNS BIT	---Data type for 0 or 1 values only
AS
	BEGIN
		DECLARE @ck_val INT
			
			SELECT @ck_val = orderid
			FROM orders
			WHERE orderid = @order_num_parameter

			IF @ck_val IS NULL 
				SET @ck_val = 0
			ELSE
				SET @ck_val = 1

		RETURN @ck_val
	END

SELECT dbo.ord_ck_fun(10257)
SELECT dbo.ord_ck_fun(10009)

--------USING FUNCTION IN STORED PROCEDURE -----------------

CREATE PROCEDURE ord_details_pro
(@ord_num INT)
AS
	DECLARE @result BIT

	SET @result = dbo.ord_ck_fun (@ord_num) ---using function for checking the existens of the order id

	IF @result = 0 
			PRINT 'ORDER DOES NOT EXISTS'
		ELSE
			SELECT * FROM orders
			WHERE orderid = @ord_num

---EXECUTING THE PROCEDURE---
EXEC ord_details_pro 10257	---Existing Order

EXEC ord_details_pro 10009	---Non Existing Order

--------------------------------

CREATE FUNCTION dbo.prod_num_ex
(@prod_id INT)
 RETURNS BIT	---Data type for 0 or 1 values only
 AS
 BEGIN
 DECLARE @chk_prod INT, 
		 @return   BIT 

	SELECT @chk_prod = productID FROM products 
	WHERE productID  = @prod_id

	IF @chk_prod IS NULL 
		SET @return = 0
	ELSE
		SET @return = 1

RETURN @return
END

SELECT dbo.prod_num_ex(7)

SELECT dbo.prod_num_ex(999)
------------------------------------------
CREATE PROCEDURE update_up_proc
(@prod_id int, 
 @new_price  int )
 AS
 DECLARE
		@chk_ex bit 
 BEGIN 
		SET @chk_ex = dbo.prod_num_ex(@prod_id)
		IF  @chk_ex = 1 
		BEGIN 
			UPDATE products 
			SET    unitPrice = @new_price
			WHERE  productID = @prod_id
			PRINT 'Product was updated' 
		END 
		ELSE 
			PRINT 'No such Product ID' 
 END 

 EXEC update_up_proc 444 , 60

 EXEC update_up_proc 1 , 60
 
 select * from products where productID = 1

------------------------------------
--- INLINE TABLE-VALUED FUNCTION ---
------------------------------------

--- CREATING INLINE TABLE-VALUED FUNCTION ---
CREATE FUNCTION dbo.emp_for_mngr		
(@mngr_id INT)
 RETURNS TABLE
AS
 
	RETURN	(SELECT firstname, lastname, hiredate
			 FROM employees
			 WHERE reportsto = @mngr_id)
 
SELECT * from dbo.emp_for_mngr(2)

---------USING INLINE TABLE-VALUED FUNCTION---------

SELECT * FROM dbo.emp_for_mngr(2)

SELECT * FROM dbo.emp_for_mngr(5)

SELECT * FROM dbo.emp_for_mngr(9)

-------------------------------------------------------------------------------
-- Multi-statement TABLE-VALUED FUNCTION
-------------------------------------------------------------------------------

CREATE FUNCTION fn_Employees
   (@length nvarchar(9))
RETURNS @fn_Employees TABLE
   (EmployeeID int PRIMARY KEY NOT NULL,
   [Employee Name] Nvarchar(61) NOT NULL)
AS
BEGIN
   IF @length = 'ShortName'
      INSERT @fn_Employees SELECT EmployeeID, LastName 
      FROM Employees
   ELSE IF @length = 'LongName'
      INSERT @fn_Employees SELECT EmployeeID, 
      (FirstName + ' ' + LastName) FROM Employees
RETURN
END

SELECT * FROM fn_Employees('ShortName')

SELECT * FROM fn_Employees('LongName')


---------------------------------------
--- Arithmetic Progression Function ---
---------------------------------------

-- An = a1 + (n-1) * d --
-- 1, 3, 5, 7, 9, 11

-- An = 1
-- d = 2
-- n = 3

-- An = 1 + (3-1) * 2
-- An = 5


----------------------------------------
Create Function AP
(@n_val int, @d_val int, @a1_val int)
returns int
As
begin
Declare @v_result int
	set @v_result = @a1_val + (@n_val -1) * @d_val
	
	return @v_result
					
end

select dbo.AP(3, 2, 1)