首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >设置存储过程参数时出现mysql语法错误

问设置存储过程参数时出现mysql语法错误
EN

Stack Overflow用户
提问于 2016-07-16 14:46:23
回答 2查看 103关注 0票数 1

我写了mysql存储过程的代码,它显示了语法错误,我不知道我做错了什么。有谁能帮帮我。

代码语言:javascript
复制
DELIMITER $$

DROP PROCEDURE IF EXISTS `aad_adr`.` PROCEDURE MonthlySalesReport(fromdate DATE,todate DATE)` $$
CREATE PROCEDURE `aad_adr`.` PROCEDURE MonthlySalesReport(fromdate DATE,todate DATE)` ()
BEGIN
Declare fd DATE;
Declare ed DATE;
SET fd=fromdate;
SET ed=todate;
WHILE DATE(fd)<=DATE(ed)DO
select bill_master.bill_no,DATE_FORMAT(bill_master.bill_date, '%y/%m/%d') AS 'formatted_date',transaction.product_id,transaction.tax_amount,transaction.amount,transaction.amount-transaction.tax_amount as 'without_tax ',product_master.Product_name,product_master.vat from bill_master inner join transaction on bill_master.bill_no=transaction.bill_no inner join product_master on transaction.product_id=product_master.product_id where vat='14.50' and DATE_FORMAT(bill_master.bill_date, '%y/%m/%d') fd;
SET fd=DATE_ADD(fd,INTERVAL 1 DAY);
END WHILE;
END $$

DELIMITER;

错误:

代码语言:javascript
复制
Script line: 4  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 'fd;
SET fd=DATE_ADD(fd,INTERVAL 1 DAY);
END WHILE;
END' at line 8
EN

回答 2

Stack Overflow用户

发布于 2016-07-16 15:26:32

select查询错误您调用了错误的where条件。所以只需删除where子句中的DATE_FORMAT,并需要再做一次修改,最后将DELIMITER;替换为DELIMITER$$即可。

更新1:修改了SP的有效名称。

所以更新后的代码是:

代码语言:javascript
复制
DELIMITER $$

DROP PROCEDURE IF EXISTS `aad_adr`.`MonthlySalesReport` $$
CREATE PROCEDURE `aad_adr`.`MonthlySalesReport`(IN fromdate DATE,IN todate DATE)
     BEGIN
       DECLARE fd DATE;
       DECLARE ed DATE;
       SET fd=fromdate;
       SET ed=todate;
       WHILE DATE(fd)<=DATE(ed)DO
             SELECT bill_master.bill_no,DATE_FORMAT(bill_master.bill_date, '%y/%m/%d') AS 'formatted_date',transaction.product_id,transaction.tax_amount,transaction.amount,transaction.amount-transaction.tax_amount AS 'without_tax ',product_master.Product_name,product_master.vat FROM bill_master INNER JOIN TRANSACTION ON bill_master.bill_no=transaction.bill_no INNER JOIN product_master ON transaction.product_id=product_master.product_id WHERE vat='14.50' AND bill_master.bill_date=fd;
          SET fd=DATE_ADD(fd,INTERVAL 1 DAY);
       END WHILE;
    END $$
DELIMITER$$
票数 1
EN

Stack Overflow用户

发布于 2016-07-16 15:26:09

首先你有两个错误:第一个错误是过程的名称

代码语言:javascript
复制
CREATE PROCEDURE `aad_adr`.` PROCEDURE MonthlySalesReport(fromdate DATE,todate DATE)` ()

->

代码语言:javascript
复制
CREATE PROCEDURE `aad_adr`.`MonthlySalesReport`(fromdate DATE,todate DATE)

所以你的程序应该是这样的:

代码语言:javascript
复制
DELIMITER $$
DROP PROCEDURE IF EXISTS `aad_adr`.`MonthlySalesReport`$$
CREATE PROCEDURE `aad_adr`.`MonthlySalesReport`(fromdate DATE,todate DATE)
BEGIN
Declare fd DATE;
Declare ed DATE;
SET fd=fromdate;
SET ed=todate;
WHILE DATE(fd)<=DATE(ed)DO
select 
bill_master.bill_no,
DATE_FORMAT(bill_master.bill_date,'%y/%m/%d') AS 'formatted_date',
transaction.product_id,
transaction.tax_amount,
transaction.amount,
transaction.amount-transaction.tax_amount as 'without_tax ',
product_master.Product_name,
product_master.vat 
from 
bill_master inner join transaction on bill_master.bill_no=transaction.bill_no 
inner join product_master on transaction.product_id=product_master.product_id 
where
vat='14.50' and vat='14.50' and DATE_FORMAT(bill_master.bill_date, '%y/%m/%d');
SET fd=DATE_ADD(fd,INTERVAL 1 DAY);
END WHILE;
END $$
DELIMITER ;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38408578

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档