php - 存储过程与 mysql 一起工作，但 php 出现错误

Question

我为报告编写了一个存储过程，它在 mysql 上运行良好，但是当我从 PHP 调用它时，出现错误:

SQLSTATE[42000]: Syntax error or access violation: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

PHP:

$crm_reports = "CALL CRM_Report_Total_Prize_Redemption('".$CrmreportsFromDate."','".$CrmreportsToDate."',$CampaignID);";
$total_prize =  Yii::app()->db->createCommand($crm_reports)->queryAll();

这是我的存储过程

CREATE DEFINER=`esp_reward`@`%` PROCEDURE `CRM_Report_Total_Prize_Redemption`(
                        IN v_StartDate DATETIME,
                        IN v_EndDate DATETIME,
                        IN v_CampaignID INT
                    )
BEGIN
    DROP TEMPORARY TABLE IF EXISTS temp_prize_redemption;
    CREATE TEMPORARY TABLE temp_prize_redemption (
            ItemID INT(10)
            ,Qty INT(11)
            ,BenefitName VARCHAR(500)
    );
    /************************************* Item Data ****************************/
    SET @v_SelectPhysicalItemForPickWin = CONCAT(" SELECT cpw.ItemID AS PrizeID,cpw.Benefit AS BenefitName
                ,COUNT(DISTINCT PickWinWinnerID) AS Qty
                ,cpw.VoucherID,cpw.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID 
                INNER JOIN crm_pickwin_winner AS cpw ON cpw.PickWinID = cr.PickWinID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND cpw.BenefitType  = '1'
                GROUP BY PrizeID ");        

    SET @v_SelectPhysicalItemForPromotion = CONCAT(" SELECT crb.ItemID  AS PrizeID,crb.BenefitName
                ,COUNT(DISTINCT ID) AS Qty,crb.VoucherID,crb.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID AND cr.TypeID = '1' 
                INNER JOIN crm_receipt_benefit AS crb ON crb.PromotionID = cr.PromotionID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND crb.BenefitTypeID  = '1'
                GROUP BY PrizeID ");

    SET @ItemData = CONCAT(" SELECT PrizeID,SUM(Qty) AS Total,BenefitName FROM ( ",@v_SelectPhysicalItemForPickWin," UNION ",@v_SelectPhysicalItem, " ) AS  ItemData GROUP BY PrizeID ");           

    SET @v_data = CONCAT("INSERT INTO temp_prize_redemption", @ItemData);
    PREPARE s1 FROM @v_data;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;  
    /************************************* e-Voucher Data ****************************/
    SET @v_SelecteVoucherForPickWin = CONCAT(" SELECT cpw.VoucherID AS PrizeID,cpw.Benefit AS BenefitName
                ,COUNT(DISTINCT VoucherID) AS Qty
                ,cpw.VoucherID,cpw.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID 
                INNER JOIN crm_pickwin_winner AS cpw ON cpw.PickWinID = cr.PickWinID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND cpw.BenefitType  = '3'
                GROUP BY PrizeID ");        

    SET @v_SelecteVoucherForPromotion = CONCAT(" SELECT crb.VoucherID  AS PrizeID,crb.BenefitName
                ,COUNT(DISTINCT VoucherID) AS Qty,crb.VoucherID,crb.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID AND cr.TypeID = '1' 
                INNER JOIN crm_receipt_benefit AS crb ON crb.PromotionID = cr.PromotionID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND crb.BenefitTypeID  = '3'
                GROUP BY PrizeID ");

    SET @eVoucherData = CONCAT(" SELECT PrizeID,SUM(Qty) AS Total,BenefitName FROM ( ",@v_SelecteVoucherForPickWin," UNION ",@v_SelecteVoucherForPromotion, " ) AS  ItemData GROUP BY PrizeID ");           

    SET @v_data = CONCAT("INSERT INTO temp_prize_redemption", @eVoucherData);
    PREPARE s2 FROM @v_data;
    EXECUTE s2;
    DEALLOCATE PREPARE s2;  
    /************************************* Physical Voucher Data ****************************/
    SET @v_SelectPhysicalVoucherForPickWin = CONCAT(" SELECT cpw.VoucherAmount AS PrizeID,cpw.Benefit AS BenefitName
                ,COUNT(DISTINCT VoucherAmount) AS Qty
                ,cpw.VoucherID,cpw.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID 
                INNER JOIN crm_pickwin_winner AS cpw ON cpw.PickWinID = cr.PickWinID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND cpw.BenefitType  = '2'
                GROUP BY PrizeID ");        

    SET @v_SelectPhysicalVoucherForPromotion = CONCAT(" SELECT crb.VoucherAmount  AS PrizeID,crb.BenefitName
                ,COUNT(DISTINCT VoucherAmount) AS Qty,crb.VoucherID,crb.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID AND cr.TypeID = '1' 
                INNER JOIN crm_receipt_benefit AS crb ON crb.PromotionID = cr.PromotionID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND crb.BenefitTypeID  = '2'
                GROUP BY PrizeID ");

    SET @PhysicalVoucherVoucherData = CONCAT(" SELECT PrizeID,SUM(Qty) AS Total,BenefitName FROM ( ",@v_SelectPhysicalVoucherForPickWin," UNION ",@v_SelectPhysicalVoucherForPromotion, " ) AS  ItemData GROUP BY PrizeID ");           

    SET @v_data = CONCAT("INSERT INTO temp_prize_redemption", @PhysicalVoucherVoucherData);
    PREPARE s3 FROM @v_data;
    EXECUTE s3;
    DEALLOCATE PREPARE s3;  
    SELECT * FROM temp_prize_redemption;
    END$$

任何人都可以调查一下并给我任何提示，以便我解决这个问题

Answer 1

这应该是评论，但是有点长。

当我有一两个星期的空闲时间时，我将开始尝试理解您的 SQL - 但是 PREPARE/EXECUTE 的用例范围很窄，这似乎不是其中之一。

您的 PHP 代码(仍然)缺少关键部分。

您的插入语句没有明确说明输入也应该匹配的列(并不总是致命的)。

偶然有人可能会发布与您的问题相匹配的解决方案，但如果没有发生，您应该将 SQL 和 PHP 减少到重现错误所需的最低限度。