MySql query runs but it doesn't work in sp

Question

Here is my sp query:

  -- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `USP_GetUserOrders`(UserId INT)
BEGIN
SELECT op.OrderId,
    O.Number,    
    SUM(op.Price) Price,
    (SELECT CONCAT(A.Detail, ' ',C.Name, ' / ', Ci.Name) FROM kobiakinlar.Address AS A
                INNER JOIN County AS C ON C.CountyId = A.CountyId
                INNER JOIN City AS Ci ON C.CityId = Ci.CityId
                WHERE UserId = O.UserId) AS UserAddress,
   ( SELECT CASE WHEN O.Status =0 THEN 'Onay Bekliyor'  WHEN O.Status =1 THEN 'Onaylandı' WHEN O.Status = 2 THEN 'Reddedildi' END) Status,
    O.Creation,
    ( SELECT CASE WHEN O.IsDelivered =0 THEN 'Teslim Edilmedi' ELSE 'Teslim Edildi' END) IsDelivered,
    group_concat(P.Name) Product
FROM
    kobiakinlar.product P
        JOIN
    kobiakinlar.orderproduct op ON op.ProductId = P.productId
        JOIN
    kobiakinlar.order O ON O.orderId = op.OrderId
   JOIN 
kobiakinlar.address A ON A.addressId = O.AddressId
WHERE O.UserId = UserId
GROUP BY op.OrderId;
END

It returns Error Code: 1242. Subquery returns more than 1 row when I CALL USP_GetUserOrders(3)

But I run only sql in query tab, it runs and return what I want. You can see query's result in image:

enter image description here

Do you have any suggestion?

Gordon Linoff · Accepted Answer · 2012-11-12 18:59:41Z

I'm pretty sure the reason is the confusion between UserId and o.UserId.

In the query context, it does not know that you mean the argument to the sp. Change the name of the arguemnt to something like "arg_UserId" and substitute that in the query where appropriate.

You can also simplify your query syntax. The SELECT outside the case statements is redundant. Also, assuming that the joins to County and City are always 1-1, you can rewrite the query as:

SELECT op.OrderId, O.Number,  SUM(op.Price) Price,
       CONCAT(A.Detail, ' ', C.Name, ' / ', Ci.Name) AS UserAddress,
       (CASE WHEN O.Status =0 THEN 'Onay Bekliyor'  WHEN O.Status =1 THEN 'Onaylandı' WHEN O.Status = 2 THEN 'Reddedildi' END) Status,
       O.Creation,
       (CASE WHEN O.IsDelivered =0 THEN 'Teslim Edilmedi' ELSE 'Teslim Edildi' END) IsDelivered,
       group_concat(P.Name) as Product
FROM kobiakinlar.product P JOIN
     kobiakinlar.orderproduct op
     ON op.ProductId = P.productId JOIN
     kobiakinlar.order O
     ON O.orderId = op.OrderId JOIN 
     kobiakinlar.address A ON A.addressId = O.AddressId join
     County C
     ON C.CountyId = A.CountyId join
     City AS Ci
     ON C.CityId = Ci.CityId
WHERE O.UserId = arg_UserId
GROUP BY op.OrderId;

Collectives™ on Stack Overflow

MySql query runs but it doesn't work in sp

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related