How and what can I correct and compete in my tsql code?

Question

Write an SP that, given as input the customer code CustomerID and the category code CategoryID, checks whether or not the customer has placed orders for products in the specified category. In the event that products are found to have been ordered, calculate the number of products ordered and the total amount ordered, printing an information message on the screen according to the situation found:

a.      "The customer NameCustomer (CustomerID) purchased n products for a total of m pieces of the category NameCategory (CategoryID)"

b.      "The customer NameCustomer (CustomerID) has not purchased products from the category NameCategory (CategoryID)"

This what i wrote till now, I'm missing if else clauses:

CREATE PROCEDURE dbo.PlacedOrders
AS
BEGIN
DECLARE @CategoryID INT,
        @CustomerID INT,
        @nProducts int,
        @totalpieces int
    SELECT @CategoryID=CategoryID, @CustomerID, @nProducts, @totalpieces
    from Categories cat JOIN(select @CustomerID=customerid, @nProducts=COUNT(ProductId),      @totalpieces=sum(Quantity)
    from Orders o join [Order Details] od on o.OrderID=od.OrderID ) as x on cat.CategoryID=x.CategoryID
END

I receive only syntax error, how can I resolve and complete it?

UPDATE:

CREATE PROCEDURE dbo.PlacedOrders
AS
BEGIN
DECLARE @CategoryID INT,
        @CustomerID INT,
        @nProducts int,
        @totalpieces int
    SELECT @CategoryID=[CategoryID], @CustomerID=customerId, @nProducts=count(Productid), @totalpieces=sum(Quantity)
    from [dbo].[Categories] cat JOIN (select customerid,ProductId,Quantity
                            from Orders o join [Order Details] od on o.OrderID=od.OrderID GROUP by Customerid,productid,quantity ) tab on cat.CategoryID=tab.CategoryID
END

i receive only this error now:

The column name 'CategoryID' is not valid.

Answer 1

welcome to Stack Overflow.

From your question, it is not very clear how the Orders table relates to the Categories table. But herein lies our problem: You create a subquery (tab) which does not, in fact, contains the [CategoryId] column. A join with a subquery can only consist of elements which are present within this subquery (or derivatives of this information). You only have CustomerId, ProductId and Quantity available.

CREATE PROCEDURE [dbo].[PlacedOrders] AS
BEGIN
DECLARE @CategoryID INT,
    @CustomerID INT,
    @nProducts INT,
    @totalpieces INT

SELECT @CategoryID = [CategoryId]
    , @CustomerID = [CustomerId]
    , @nProducts = COUNT([ProductId])
    , @totalpieces = SUM([Quantity])
FROM [dbo].[Categories] cat
JOIN (
    SELECT [CustomerID]
        , [ProductId]
        , [Quantity]
    FROM [dbo].[Orders] o 
    JOIN [dbo].[Order Details] od ON o.[OrderID] = od.[OrderID]
    GROUP by [CustomerId], [ProductId], [Quantity]
) tab ON cat.[CategoryId] = tab.[CategoryId] --- here is your problem
--Add CategoryId to the subquery tab, and you'll get your result set
END;