通过mysql存储过程的“where子句”中的未知列

balp4ylt 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(302)

我使用一个带有准备语句的存储过程从字符串中查找行。
看起来像这样

CREATE DEFINER=`y0y0`@`%` PROCEDURE `SP_GEN_CASH_SOD_TXT_FILE`(IN oRETURNNO varchar(20))
BEGIN
    SET @returnNo = oRETURNNO;
    SET @sitePath = (select site_path from sap_transfer_data WHERE payer_payment_type = 'C' and trans_status ='I' and return_no = @returnNo LIMIT 1);
    SET @outputPath = CONCAT("/cash/","SAP_",@sitePath,"_SP_",@returnNo,"_", date_format(CURDATE(), '%Y%m%d'),".txt");

    SET @row_number = 0;
    SET @sqlCommand = CONCAT('SELECT "IsCashYesOrNo^Transaction type^Line No.^SAP Sales Organization^Date of Receive^HIS Unique ID^Paycode code (Internal)^Lab Number^Test Item Code^Test Item Description^Sequence^Customer Material No.^Lab site code^Sent Test^Lab Department Code^Lab Department Name^Hospital Number^Address2^IN/OUT^Priority^Auto Add TC^Patient first name^Patient last name^Patient address^Hospital Department^Hospital Location code^Hospital Location description^Quantity^UOM^Ref. No.^Date of Enter^Pathologist Code 1^Pathologist Level 1^Pathologist Code 2^Pathologist Level 2^Pathologist Code 3^Pathologist Level 3^Pathologist Code 4^Pathologist Level 4^Pathologist Code 5^Pathologist Level 5^Pathologist Code 6^Pathologist Level 6^Pathologist Code 7^Pathologist Level 7^Pathologist Code 8^Pathologist Level 8" as texts from dual
    UNION ALL
    SELECT CONCAT("Yes", "^", trans_status, "^", CAST((@row_number:=@row_number + 1) as char), "^", "0105", "^", return_date, "^", return_no, "|",  item_code, "|", "1", "^", payer_ship_to, "^", return_no, "^", item_code, "^",
    "^", "1", "^", "^", lab_site_code, "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", cust_code, "^", cust_name, "^", return_qty, "^", "^", ref_req_no, "^",  
    "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^", "^") as texts 
    INTO OUTFILE ', char(39), @outputPath, char(39),
    ' LINES TERMINATED BY ', char(39),'\r\n', char(39),
    ' FROM sap_transfer_data
    WHERE payer_payment_type = "C" and trans_status = "I" and return_no = ', @returnNo);

    prepare s1 from @sqlCommand;
    execute s1; deallocate prepare s1;

    update sap_transfer_data set delivery_date = CURDATE(), trans_fag = true where return_no = @returnNo;
END

然后，我使用以下方法调用该过程：

call SP_GEN_CASH_SOD_TXT_FILE ('RT20190101039354');

但是，我收到这个错误：

Error Code: 1054. Unknown column 'RT20190101039354' in 'where clause'

你有什么建议给我吗？
先谢谢你^_^

sql mysql stored-procedures prepared-statement

来源：https://stackoverflow.com/questions/54001025/unknown-column-in-where-clause-with-stored-procedure-via-mysql

1条答案

按热度按时间

rjjhvcjd1#

设置@sqlcommand=concat（'。。。其中付款人付款类型为“c”，交易状态为“i”，返回号为“，@returnno）；
为了 @returnNo = 'RT20190101039354' ，这就结束了 @sqlCommand 看起来像

WHERE payer_payment_type = "C" and trans_status = "I" and return_no = RT20190101039354

因此引擎假设它是一个列名。
您希望a）在字符串文字周围使用双引号进行strop，并且只使用单引号，b）特别是将代码更改为：

SET @sqlCommand = CONCAT('...
...
WHERE payer_payment_type = ''C'' and trans_status = ''I'' and return_no = ?');

execute s1 USING @returnNo; deallocate prepare s1;

问号是一个占位符，使用 USING 从句至 EXECUTE .

赞(0）回复(0）举报 2021-06-17

我来回答

通过mysql存储过程的“where子句”中的未知列

1条答案

相关问题

热门标签

最新问答