Create a Procedure or run the below procedure on your machine it will work
No need inner join for deleting reference key u can just delete as it is
Create Procedure DeleteProduct
@PID varchar(100)
AS
begin transaction
DELETE tblOrderItems where Pid=@PID
DELETE tblProducts where Pid=@PID
IF @@error <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END