-- Stored Procedures
/*
Stored procedures are named groups of Transact-SQL (T-SQL) statements that can be used and 
reused whenever they're needed. Stored procedures can return results, manipulate data, 
and perform administrative actions on the server. You may need to execute 
stored procedures that someone else has created or create your own.

https://learn.microsoft.com/en-us/training/modules/create-stored-procedures-table-valued-functions/1-introduction

*/

use AdventureWorksDW2020;

go
CREATE PROCEDURE TopProducts AS
SELECT TOP(10) p.Class, sum(p.listprice) as total_price
    FROM DimProduct as p
    GROUP BY p.Class
    ORDER BY sum(p.listprice) DESC;

go

exec TopProducts
go

-- Dynamic sql exec
/*
Dynamic SQL allows you to build a character string that can be executed as T-SQL as an alternative 
to stored procedures. 
Dynamic SQL is useful when you don't know certain values until execution time.
*/

DECLARE @sqlstring AS VARCHAR(1000);
    SET @sqlstring='SELECT customerid, companyname, firstname, lastname 
    FROM DimCustomer;'
EXEC(@sqlstring);
GO

DECLARE @sqlstring1 AS VARCHAR(1000);
    SET @sqlstring1='SELECT c.customerkey, firstname, lastname 
    FROM DimCustomer as c;'
EXEC(@sqlstring1);
GO

--Sp_executesql allows you to execute a T-SQL statement with parameters. Sp_executesql can be used instead of 
--stored procedures when you want to pass a different value to the statement.

DECLARE @sqlstring2 NVARCHAR(1000);
SET @SqlString2 =
    N'SELECT TOP(10) p.Class, sum(p.listprice) as total_price
    FROM DimProduct as p
    GROUP BY p.Class;'
EXECUTE sp_executesql @SqlString2;

go
-- with parameter

EXECUTE sp_executesql   
          N'SELECT * FROM DimCustomer   
          WHERE customerkey = @cid',  
          N'@cid nvarchar(128)',  
          @cid = 11000;

go

					-- user defiend functions

--User-defined functions (UDF) are similar to stored procedures in that they re stored separately 
--from tables in the database. These functions accept parameters, perform an action, and then 
--return the action result as a single (scalar) value or a result set (table-valued).

CREATE FUNCTION ProductsInfo(@rec int)  
RETURNS TABLE  
AS  
RETURN  
    SELECT top(@rec) ProductKey, EnglishProductName, ProductLine  
    FROM DimProduct  
go

-- calling function
select * from ProductsInfo(20)
go
--multistatment TVF

CREATE FUNCTION OrderStatus 
     ( @CustomerID int )
RETURNS 
@Results TABLE 
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
-- 1st statement which capture results from 2nd statement
     INSERT INTO @Results
-- 2nd statement which extract data from db
     SELECT top(10) SC.Customerkey, soh.OrderDate
     FROM DimCustomer AS SC 
     INNER JOIN FactInternetSales AS SOH 
        ON SC.CustomerKey = SOH.CustomerKey
     
 RETURN;
END;
GO;

select * from OrderStatus(11000);

go

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FactInternetSales'

go

select ProductKey, UnitPrice, OrderDate from FactInternetSales where ProductKey=310 and OrderDate='2018-01-11';
go
--scalar valued functions

CREATE FUNCTION Productpricevar
(@ProductID int, @OrderDate date)
RETURNS decimal 
AS 
BEGIN
    DECLARE @ListPrice decimal;
        SELECT @ListPrice = f.UnitPrice from dimProduct as p
        --select f.UnitPrice FROM dimProduct as p 
        INNER JOIN FactInternetSales as f 
        ON p.ProductKey = f.ProductKey 
		--where p.ProductKey = 310 and f.OrderDate = '2018-01-11'
           and p.ProductKey = @ProductID 
            AND f.OrderDate = @OrderDate
    RETURN @ListPrice;
END;
GO

select [dbo].Productpricevar(310, '2018-01-11');