select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral = 298 and igtxtParentCardNoTxt = 000005 ) and placeCmb = 1 ;自定义: i_First_Introduer ;
j_First_Introduer
k_First_Introduer;"select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral = '" + i_First_Introduer + "' and igtxtParentCardNoTxt = '" + j_First_Introduer + "') and placeCmb = " + k_First_Introduer ;
这样就报错.. 是不是我拼接的不对,还是存储过程不能使用自定义的变量..刚刚接触存储过程.别笑话我..
j_First_Introduer
k_First_Introduer;"select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral = '" + i_First_Introduer + "' and igtxtParentCardNoTxt = '" + j_First_Introduer + "') and placeCmb = " + k_First_Introduer ;
这样就报错.. 是不是我拼接的不对,还是存储过程不能使用自定义的变量..刚刚接触存储过程.别笑话我..
---------------------------------------------------------------------------------
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `test`.`touch_member;
`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN declare v_flag varchar(100);
set v_flag = "select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral ='" + i_First_Introduer + "' and igtxtParentCardNoTxt = '" + j_First_Introduer +"') and placeCmb = '" + k_First_Introduer ;
EXECUTE(v_flag);
END$$DELIMITER ;
---------------------------------------------------------------------------
错误码: 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 '(v_flag);
END' at line 13
应该就是我拼接的不对把..concat 能帮我写一下么..我学习学习.
set v_flag = concat ('select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral =',i_First_Introduer ,' and igtxtParentCardNoTxt = ', j_First_Introduer ,') and placeCmb = ',k_First_Introduer) ;
EXECUTE(v_flag);错误码: 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 '(v_flag);
prepare ee from @v_flag;
EXECUTE ee ;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
如果你已经熟悉MS SQL SERVER,则你可以直接参考一下MYSQL的官方文档中的语法说明。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
BEGIN
declare v_flag varchar(100);
set @v_flag = concat("select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral ='", i_First_Introduer , "' and igtxtParentCardNoTxt = '" , j_First_Introduer ,"') and placeCmb = '" , k_First_Introduer ) ;
prepare ee from @v_flag;
EXECUTE ee ;
END$$DELIMITER ;
---------------------------------------------------------------------------------call touch_member('5','6','6');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 ''6' at line 1
select @v_flag;
prepare ee from @v_flag;加上这句,看一下你的SQL语句是什么,应该是语法上出错了。
BEGIN
set @v_flag = concat("select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral ='", i_First_Introduer , "' and igtxtParentCardNoTxt = '" , j_First_Introduer ,"') and placeCmb = '" , k_First_Introduer,"'" ) ;
-- select @v_flag;
prepare ee from @v_flag;
EXECUTE ee ;
DEALLOCATE PREPARE ee;
END$$DELIMITER ;你最后少了一个 ' 引号。加上就行了。mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `touch_member`$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE `touch_member`(IN i_First_Introduer varchar(20),IN j_Fir
st_Introduer varchar(20),IN k_First_Introduer varchar(20))
-> BEGIN
-> set @v_flag = concat("select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_managementwhere pro_integral ='", i_First_Introduer , "' and igtxtParentCardNoTxt = '" , j_First_Introduer ,"') and placeCmb = '" , k_First_Introduer,"'" ) ;
-> select @v_flag;
-> prepare ee from @v_flag;
-> EXECUTE ee ;
-> DEALLOCATE PREPARE ee;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> call touch_member('5','6','6');
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------+
| @v_flag |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------+
| select count(pro_integral) from sales_product_management where igtxtParentCard
NoTxt in ( select * from sales_product_management where pro_integral ='5' and
igtxtParentCardNoTxt = '6') and placeCmb = '6' |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------+
1 row in set (0.01 sec)ERROR 1146 (42S02): Table 'csdn.sales_product_management' doesn't exist
mysql>
prepare ee from @v_flag;
EXECUTE ee ;
Operand should contain 1 column(s)
我运行完这是什么错误??
BEGIN
select count(pro_integral) from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral = 298 and igtxtParentCardNoTxt = 000005 ) and placeCmb = 1 ; END$$DELIMITER ;call touch_('2','3','4');这样就显示正常 无论有没有数据
---------------------------------------------------------------------------------------DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`touch_`$$CREATE PROCEDURE `touch_`(IN i_First_Introduer varchar(20),IN j_First_Introduer varchar(20),IN k_First_Introduer varchar(20))
BEGIN
set @v_flag = concat("select count(pro_integral) as a from sales_product_management where igtxtParentCardNoTxt in ( select * from sales_product_management where pro_integral ='", i_First_Introduer , "' and igtxtParentCardNoTxt = '" , j_First_Introduer ,"') and placeCmb = '" , k_First_Introduer,"'" ) ;
prepare ee from @v_flag;
EXECUTE ee ;
DEALLOCATE PREPARE ee; END$$DELIMITER ;call touch_('2','3','4');这样就报错 错误码: 1241
Operand should contain 1 column(s)
看看