sql server - Trying to create stored procedure with parameter and multiple joins

Question

I'm trying to create a stored procedure with one parameter and joining three tables: OrdersOrders, Ordered_ProductsOrdered_Products and ProductsProducts. I want columns from all the tables that does not have matching tables. The parameter is used in java for the costumer to be able to search order based on their user ID.

This is what I've come up with:

CREATE PROCEDURE sp_seeOrder
    @user_id
AS
    SELECT 
        O.order_id, O.order_quantity, O.user_id, O.Status, O.order_date, 
        O_P.orderID, O_P.product_code, 
        P.product_id, P.product_name, P.price
    FROM 
        Orders AS O
    FULL JOIN 
        Ordered_Products ON O.order_id = O_P.orderID,
    RIGHT JOIN 
        Products P ON O_P.product_code = P.product_id
    WHERE 
        user_id = @user_id;

I get multiple errors:

Msg 156, Level 15, State 1, Procedure sp_seeOrder, Line 4 [Batch Start Line 213]
Incorrect syntax near the keyword 'SELECT'.

Msg 156, Level 15, State 1, Procedure sp_seeOrder, Line 9 [Batch Start Line 213]
Incorrect syntax near the keyword 'RIGHT'.

What have I done wrong?

question from:https://stackoverflow.com/questions/65904589/trying-to-create-stored-procedure-with-parameter-and-multiple-joins

Answer 1

Put a data type in the @user_id parameter. I assumed that it is an INT since it is an ID. And remove the extra , after the "ON O.order_id = O_P.orderID". The corrected query should look like this:

CREATE PROC sp_seeOrder
@user_id INT
AS
SELECT 
    O.order_id, O.order_quantity, O.user_id, O.Status, O.order_date, O_P.orderID, 
    O_P.product_code, P.product_id, P.product_name, P.price
FROM Orders AS O
FULL JOIN Ordered_Products AS O_P
    ON O.order_id = O_P.orderID
RIGHT JOIN Products P
    ON O_P.product_code = P.product_id
WHERE user_id = @user_id;