USE Atlantic;
GO

IF EXISTS (SELECT *	FROM sys.objects
			WHERE object_id = OBJECT_id (N'dbo.udf_Customer_Purchase_Details')
			AND type IN(N'FN', N'TF', N'IF'))
DROP FUNCTION dbo.udf_Customer_Purchase_Details
GO
CREATE FUNCTION udf_Customer_Purchase_Details
(
	@cust_lastname VARCHAR(50)
)
RETURNS TABLE
AS 
RETURN
(
SELECT C.customer_id
, C.cust_firstname + ' ' + C.cust_lastname AS cust_full_name
, C.cust_address
, C.cust_city
, O.orders_id
, O.orders_date
, P.product_id
, P.product_name
, P.product_cost_price
, D.destination_name

FROM Customer AS C INNER JOIN Orders AS O
	ON C.customer_id = O.customer_id
	INNER JOIN Product AS P
	ON O.product_id = P.product_id
	INNER JOIN Category AS CA
	ON P.category_id = CA.category_id
	INNER JOIN Destination AS D
	ON O.destination_id = D.destination_id
	WHERE C.cust_lastname = @cust_lastname	
	--  meeting a single condition - last_name filtered by the variable
)