functions

---------------------------------------------------------------------------------------------
------------------------------------------ FUNCTIONS ----------------------------------------
---------------------------------------------------------------------------------------------
-- зад 1. scalar-valued function
CREATE FUNCTION CUSTOMERS_COUNT (@CUST_ID INT)
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @CNT INT, @NAME VARCHAR(30)

    SELECT @NAME = C.FNAME + ' ' + C.LNAME,
           @CNT = COUNT(O.ORDER_ID)
    FROM CUSTOMERS C LEFT JOIN ORDERS O
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
    WHERE C.CUSTOMER_ID = @CUST_ID
    GROUP BY C.FNAME, C.LNAME

    RETURN 'The customer ' + @NAME + ' with (ID = ' +
            CAST(@CUST_ID AS VARCHAR) + ') made ' +
            CAST(@CNT AS VARCHAR) + ' orders.'
END

SELECT DBO.CUSTOMERS_COUNT(CUSTOMER_ID)
FROM CUSTOMERS

-- зад 2.  - table-valued function
CREATE FUNCTION COUNTRIES_BY_REGION()
RETURNS TABLE
AS
RETURN
    SELECT R.NAME [REGION NAME], C.NAME [COUNTRY NAME]
    FROM COUNTRIES C FULL JOIN REGIONS R
    ON C.REGION_ID = R.REGION_ID

SELECT *
FROM DBO.COUNTRIES_BY_REGION()
ORDER BY 1