MySql 查询运行但它在 sp 中不起作用 [英] MySql query runs but it doesn't work in sp

问题描述

这是我的 sp 查询:

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

返回错误代码:1242.当我CALL USP_GetUserOrders(3)

但我只在查询选项卡中运行 sql，它运行并返回我想要的.您可以在图像中看到查询的结果:

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

你有什么建议吗?

推荐答案

我很确定原因是 UserId 和 o.UserId 之间的混淆.

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

在查询上下文中，它不知道您指的是 sp 的参数.将争论的名称更改为类似arg_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.

您还可以简化查询语法.case 语句外的 SELECT 是多余的.此外，假设连接到县和市总是 1-1，您可以将查询重写为:

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;

这篇关于MySql 查询运行但它在 sp 中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！