SQL Northwind DB - Stored Procedures

1. Create a stored procedure to search employees by the first few letters in their last name.

CREATE PROCEDURE dbo.spSearchEmployeebyLName
    @LName nvarchar(20)

AS
    SET @LName = @LName + '%'
    SELECT * FROM Employees
    WHERE LastName LIKE @LName 
    RETURN

2. Create a stored procedure to create a new product and a new category by passing the CategoryName, ProductName, and the status (Discontinued) is False.

CREATE PROCEDURE spCreateNewProduct
    
    @CategoryName nvarchar(15),
    @ProductName nvarchar(40),
    @Status bit = 'False'

AS
    DECLARE @CatID INT;
    
    INSERT INTO Categories (CategoryName)
    VALUES ( @CategoryName )
    SET @CatID=@@IDENTITY
    
    INSERT INTO Products (ProductName, CategoryID, Discontinued) 
    VALUES (@ProductName, @CatID, @Status)
    
    RETURN

3. Create a stored procedure to show the stock level of every product in the Products table. For unit stock under 20, the stock level is low. Unit stock above 100, the stock level is high. For the rest, the stock level is medium.

CREATE PROCEDURE spShowStockLevel 
    
AS
    SELECT ProductID, ProductName, UnitsInStock,
    CASE
    WHEN UnitsInStock <20
        THEN 'LOW'
    WHEN UnitsInStock >100
        THEN 'HIGH'
    ELSE
        'MEDIUM'
    END
    AS StockLevel
    FROM Products
    RETURN

4. Create a stored procedure to display the territory status for each territory in Territories table. Territories id that starts with 9 is considered as big cities. Territories id starts with 0 or 1 is considered as small cities. Territories id starts with 2 to 6 is considered as other cities. Territories id starts with 7 or 8 is considered as popular cities.

CREATE PROCEDURE spShowTerritoryStatus

AS
    SELECT TerritoryID, TerritoryDescription,
    CASE 
        WHEN TerritoryID LIKE '9%'
            THEN 'big cities'
        WHEN TerritoryID LIKE '[0-1]%'
            THEN 'small cities'
        WHEN TerritoryID LIKE '[2-6]%'
            THEN 'other cities'
        WHEN TerritoryID LIKE '[7-8]%'
            THEN 'big cities'
    END
    AS TerritoryStatus
    FROM Territories
    
    RETURN

5. Create a stored procedure to find the new price of the first 10 products in Products table. The new price will be calculated based on the amount of percentage inputted into the procedure. The output should show the old and the new prices.

CREATE PROCEDURE spFindNewPrice
    @Percentage DECIMAL
AS
    SELECT ProductID, ProductName, UnitPrice, 
    NewUnitPrice = ROUND(( ( (@Percentage/100) +1 ) * UnitPrice ),2)     
    FROM Products
    RETURN