-- Procedura vs skalarna funkcia:
-- Procedura sa vola specialne cez EXEC/EXECUTE a skalarna funkcia sa vola v ramci SELECT dopytu. 
-- Skalarna funkcia nemoze menit data, t.j. ziaden INSERT, UPDATE, DELETE.
-- Skalarna funkcia vzdy vrati nejaku hodnotu.
-- Procedura podporuje odchytavanie vynimiek cez TRY-CATCH.
-- Procedura podporuje transakcie.

--===============================================================================================--
-- 1a. Najdime minimum dvoch cisel c1, c2 pomocou funkcie!
IF OBJECT_ID('f_mini', 'FN') IS NOT NULL DROP FUNCTION f_mini;
GO
CREATE FUNCTION f_mini( @c1 int, @c2 int ) RETURNS INT
AS
BEGIN
DECLARE @mini int;
  if @c1 < @c2
  set @mini = @c1
  else
  set @mini = @c2
  RETURN @mini
END
GO
SELECT dbo.f_mini(1, 2)  -- dbo. !!!!!!!!!!!!!!!
SELECT dbo.f_mini(11, 2)  -- dbo. !!!!!!!!!!!!!!!

--===============================================================================================--
-- 1a. Vytvorte skalarnu funkciu, ktora retazce v tvare 'Ahoj_Svet' prepise do tvaru 'ahojSvet'.

IF OBJECT_ID('CamelCase', 'FN') IS NOT NULL
    DROP FUNCTION dbo.CamelCase;
GO

CREATE FUNCTION CamelCase(@retazec NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @bezPodtrznikov NVARCHAR(MAX) = REPLACE(@retazec, '_', '');
	DECLARE @prveMalym NVARCHAR(MAX) = STUFF(@bezPodtrznikov, 1, 1, LOWER(LEFT(@bezPodtrznikov, 1)));
	RETURN @prveMalym;
END;
GO

SELECT dbo.CamelCase('Ahoj_Svet');
SELECT dbo.CamelCase('Meno_Priezvisko');

GO

--===============================================================================================--

-- 2aa. Vytvorenie dvoch stlpcov s mini a maxi!
SELECT min(x) mini, max(x) maxi 
FROM
(
SELECT 2 x
UNION
SELECT 11 x
) T


-- 2ab. Najdime minimum a maximum dvoch cisel c1, c2 pomocou funkcie!
IF OBJECT_ID('f_minimaxi', 'IF') IS NOT NULL DROP FUNCTION f_minimaxi;
GO
CREATE FUNCTION f_minimaxi( @c1 int, @c2 int ) RETURNS TABLE
AS
----BEGIN
  RETURN(	
	SELECT min(x) mini, max(x) maxi 
    FROM
	(
		SELECT @c1 x
		UNION
		SELECT @c2 x
	) T
  )
----END
GO
SELECT * FROM f_minimaxi(22,6);

--===============================================================================================--
-- UVAZUJTE databazu OsobaVztah
-- 2b. Vytvorte inline tabulkovu funkciu (IF) s celociselnym parametrom vek, ktora vrati osoby mladsie ako vek rokov.

USE OsobaVztah;

GO

IF OBJECT_ID('mladsi', 'IF') IS NOT NULL 
DROP FUNCTION Mladsi;

GO

CREATE FUNCTION mladsi(@vek int) RETURNS TABLE
AS
	RETURN (SELECT * FROM Osoba WHERE YEAR(CURRENT_TIMESTAMP) - YEAR(dat_nar) < @vek);
GO

SELECT * FROM mladsi(18);

GO

-- Skalarna funkcia vs inline funkcia:
-- Inline funkcia vracia tabulku.
-- Telo inline funkcie je tvorene iba RETURN.  
-- Inline funkcia je interne implementovana ako VIEW.
--===============================================================================================--

-- 3. Vytvorte tabulkovu funkciu s definovanymi stlpcami (TF) s cislami od min po max s danym rozostupom
IF OBJECT_ID('ftab', 'TF') IS NOT NULL DROP FUNCTION ftab;
GO
-- tabulka s cislami od min po max s danym rozostupom
CREATE FUNCTION ftab ( @min INT, @max INT, @by int )
    RETURNS @O2 TABLE ( xxx INT ) AS
BEGIN
    WHILE @min <= @max
    BEGIN
        INSERT INTO @O2 ( xxx ) VALUES ( @min )
        SET @min = @min + @by
    END
RETURN
END
GO
-- Parne cisla od min po max
SELECT * FROM ftab ( 10, 30, 2 )