How do I use temp tables in stored procedures?

Question

I started learning SQL a few days ago and I'm loving it. I have this problem with stored procedures and I have been stuck for almost a week. Please I will appreciate any help on this

Using the AdventureWorks2016 DB

Name: CREATE PROCEDURE proc_TerritoryTop5Sales_ByProduct

• Parameter: Territory Name (Passing Value)
• b. Results set: Top 5 Products by year

(The following is for the results set, which will be created in your statement in order to pass in Territory Name to receive the Top 5 Products sold (Sum of Line Total) by each Year)

Hint: You may need to use a temp table/table variable combination.

Answer 1

CREATE PROCEDURE        dbo.Proc_TerritoryTop5Sales_ByProduct
                        @TerritoryName  VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE            #ProductsList    (ProductName VARCHAR(50),TotalSales MONEY, OrderYear INT)

 INSERT INTO            #ProductsList   (ProductName,TotalSales, OrderYear)
 SELECT                 Dd.[Name], 
                        SUM(Cc.LineTotal) TotalSales, 
                        YEAR(Aa.OrderDate) [Year]
 FROM                   [Production].[Product] Dd
 FULL OUTER JOIN        [Sales].[SalesOrderDetail] Cc
 ON                     Cc.ProductID = Dd.ProductID
 FULL OUTER JOIN                [Sales].[SalesOrderHeader] Aa
 ON                     Aa.SalesOrderID = Cc.SalesOrderID
 FULL OUTER JOIN                [Sales].[SalesTerritory] Bb
 ON                     Aa.TerritoryID = Bb.TerritoryID
 WHERE                  Bb.[Name] = @TerritoryName
 GROUP BY               YEAR(OrderDate),Dd.[Name]
 ORDER BY               YEAR(OrderDate)DESC , TotalSales DESC 


 CREATE TABLE           #Top5SalesProduct   (ProductName VARCHAR(50),TotalSales MONEY, OrderYear INT)
 DECLARE                @YearlySale     INT
 SET                    @YearlySale =   (
                                            SELECT      TOP 1 OrderYear
                                            FROM        #ProductsList
                                        )
WHILE                   @YearlySale IS NOT NULL
BEGIN
INSERT  INTO            #Top5SalesProduct   (ProductName,TotalSales, OrderYear)
        SELECT          TOP 5 ProductName,TotalSales, OrderYear
        FROM            #ProductsList
        WHERE           OrderYear = @YearlySale
        ORDER BY        OrderYear DESC, TotalSales DESC 


DELETE  FROM                #ProductsList
        WHERE               OrderYear = @YearlySale
SET                         @YearlySale =   (
                                                SELECT      TOP 1 OrderYear
                                                FROM        #ProductsList
                                            )
END

SELECT      *
FROM        #Top5SalesProduct
ORDER  BY   OrderYear, TotalSales DESC

END


dbo.Proc_TerritoryTop5Sales_ByProduct 'France'

Answer 2

This is a pseudo code, but close to what you want. Substitute the column names & table name and it should take you a step closer.

CREATE PROCEDURE proc_TerritoryTop5Sales_ByProduct (@Territory VARCHAR(128))
AS 
 BEGIN 
    SELECT TOP 5 [products], [Year]  
    FROM AdventureWorks2016.SchemaName.TableName  
    WHERE Territory = @Territory 
    ORDER BY [year] DESC  
 END