Stored Procedure problem
Hello everyone! Hopefully somone can enlight me, i am hanging on this vor a few hours now :/ i have this stored procedure: CREATE PROCEDURE additems () BEGIN DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21'; DECLARE newids VARCHAR(128); SELECT GROUP_CONCAT(a_id,newids) INTO newids FROM articles WHERE article_garmmature IN (grammatures); SELECT @newids; END; What i wan't is to store the id's as a comma seperated list into the newids VAR. I get the error that there is more than one result set. I also tried group_concat, but that didn't worked :( Anyone has an idea how to store multiple ids into one VAR ? Thanks for reading! Best wishes Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure problem
Dynamic SQL would work for Imbedding String for the IN clause.
That would be too messy for such a little query.
Here is a crazy suggestion
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROUP_CONCAT(a_id,newids) INTO newids FROM articles WHERE
INSTR(CONCAT(',',grammatures,','),CONCAT(',',article_grammatures,',')) 0
SELECT @newids;
END;
By the way, is it ' article_grammature ' or ' article_garmmature ' ?
Give it a try !!!
-Original Message-
From: Barry [mailto:[EMAIL PROTECTED]
Sent: Friday, February 15, 2008 5:28 AM
To: mysql@lists.mysql.com
Subject: Stored Procedure problem
Hello everyone!
Hopefully somone can enlight me, i am hanging on this vor a few hours now :/
i have this stored procedure:
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROUP_CONCAT(a_id,newids) INTO newids
FROM articles
WHERE article_garmmature IN (grammatures);
SELECT @newids;
END;
What i wan't is to store the id's as a comma seperated list into the
newids VAR.
I get the error that there is more than one result set.
I also tried group_concat, but that didn't worked :(
Anyone has an idea how to store multiple ids into one VAR ?
Thanks for reading!
Best wishes
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Problem
Josh wrote: I posted this same item on the mysql forum but the only place that looked remotely appropriate was under the Newbie section... I'm not sure if it will be answer there so I thought I might toss it out here to see if there were any takers. I'm baffled as to why this stored procedure is acting this way. See the below sample table and examples. The query as provided doesn't parse--chgID doesn't exist. Did you try naming the sproc params differently from the corresponding columns, eg pEMPID, pBDID? PB - mysql select * from Rates; +--+--+---+-+---+ | rtID | bdID | empID | rtStartDate | rtBillingRate | +--+--+---+-+---+ |1 | NULL | NULL | -00-00 | 0.00 | |2 | NULL | 1 | 2004-01-01 | 2.00 | |3 | NULL | 1 | 2004-05-10 | 4.00 | |4 | NULL | 1 | 2005-01-10 | 6.00 | |5 | NULL | 1 | 2005-04-12 | 8.00 | |6 | NULL | 1 | 2006-01-02 | 10.00 | |8 | 37 | 1 | 2005-10-01 | 25.00 | +--+--+---+-+---+ DELIMITER $ CREATE PROCEDURE test_rate (EMPID int, BDID int, CURRENTDATE date) BEGIN SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID IS NULL and rtStartDate = CURRENTDATE)) LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and rt3.chgID IS NULL and rt3.bdID=BDID and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID=BDID and rtStartDate = CURRENTDATE)) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; END$ DELIMITER ; mysql call test_rate (1,NULL,'2005-09-01'); +---+ | rtBillingRate | +---+ | 8.00 | +---+ CORRECT! mysql call test_rate (1,37,'2005-10-10'); +---+ | rtBillingRate | +---+ | 25.00 | +---+ CORRECT! mysql call test_rate (1,NULL,'2005-10-10'); +---+ | rtBillingRate | +---+ | 0.00 | +---+ 1 row in set (0.01 sec) WRONG! This should have returned 8.00. When I run this query by itself (outside the procedure) I get the correct result: (notice I'm plugging in EMPID, BDID, and CURRENTDATE parameters) SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=1 and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID IS NULL and rtStartDate = '2005-10-10')) LEFT JOIN Rates rt3 ON (rt3.empID=1 and rt3.chgID IS NULL and rt3.bdID=NULL and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID=NULL and rtStartDate = '2005-10-10')) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; +---+ | rtBillingRate | +---+ | 8.00 | +---+ 1 row in set (0.00 sec) CORRECT! What's going wrong in the stored procedure? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Problem
Peter, nice catch. Changing the parameter names did the trick. Thanks. --- Peter Brawley [EMAIL PROTECTED] wrote: Josh wrote: I posted this same item on the mysql forum but the only place that looked remotely appropriate was under the Newbie section... I'm not sure if it will be answer there so I thought I might toss it out here to see if there were any takers. I'm baffled as to why this stored procedure is acting this way. See the below sample table and examples. The query as provided doesn't parse--chgID doesn't exist. Did you try naming the sproc params differently from the corresponding columns, eg pEMPID, pBDID? PB - mysql select * from Rates; +--+--+---+-+---+ | rtID | bdID | empID | rtStartDate | rtBillingRate | +--+--+---+-+---+ |1 | NULL | NULL | -00-00 | 0.00 | |2 | NULL | 1 | 2004-01-01 | 2.00 | |3 | NULL | 1 | 2004-05-10 | 4.00 | |4 | NULL | 1 | 2005-01-10 | 6.00 | |5 | NULL | 1 | 2005-04-12 | 8.00 | |6 | NULL | 1 | 2006-01-02 | 10.00 | |8 | 37 | 1 | 2005-10-01 | 25.00 | +--+--+---+-+---+ DELIMITER $ CREATE PROCEDURE test_rate (EMPID int, BDID int, CURRENTDATE date) BEGIN SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID IS NULL and rtStartDate = CURRENTDATE)) LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and rt3.chgID IS NULL and rt3.bdID=BDID and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID=BDID and rtStartDate = CURRENTDATE)) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; END$ DELIMITER ; mysql call test_rate (1,NULL,'2005-09-01'); +---+ | rtBillingRate | +---+ | 8.00 | +---+ CORRECT! mysql call test_rate (1,37,'2005-10-10'); +---+ | rtBillingRate | +---+ | 25.00 | +---+ CORRECT! mysql call test_rate (1,NULL,'2005-10-10'); +---+ | rtBillingRate | +---+ | 0.00 | +---+ 1 row in set (0.01 sec) WRONG! This should have returned 8.00. When I run this query by itself (outside the procedure) I get the correct result: (notice I'm plugging in EMPID, BDID, and CURRENTDATE parameters) SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=1 and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID IS NULL and rtStartDate = '2005-10-10')) LEFT JOIN Rates rt3 ON (rt3.empID=1 and rt3.chgID IS NULL and rt3.bdID=NULL and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID=NULL and rtStartDate = '2005-10-10')) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; +---+ | rtBillingRate | +---+ | 8.00 | +---+ 1 row in set (0.00 sec) CORRECT! What's going wrong in the stored procedure? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure Problem
I posted this same item on the mysql forum but the only place that looked remotely appropriate was under the Newbie section... I'm not sure if it will be answer there so I thought I might toss it out here to see if there were any takers. I'm baffled as to why this stored procedure is acting this way. See the below sample table and examples. mysql select * from Rates; +--+--+---+-+---+ | rtID | bdID | empID | rtStartDate | rtBillingRate | +--+--+---+-+---+ |1 | NULL | NULL | -00-00 | 0.00 | |2 | NULL | 1 | 2004-01-01 | 2.00 | |3 | NULL | 1 | 2004-05-10 | 4.00 | |4 | NULL | 1 | 2005-01-10 | 6.00 | |5 | NULL | 1 | 2005-04-12 | 8.00 | |6 | NULL | 1 | 2006-01-02 | 10.00 | |8 | 37 | 1 | 2005-10-01 | 25.00 | +--+--+---+-+---+ DELIMITER $ CREATE PROCEDURE test_rate (EMPID int, BDID int, CURRENTDATE date) BEGIN SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID IS NULL and rtStartDate = CURRENTDATE)) LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and rt3.chgID IS NULL and rt3.bdID=BDID and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=EMPID and chgID IS NULL and bdID=BDID and rtStartDate = CURRENTDATE)) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; END$ DELIMITER ; mysql call test_rate (1,NULL,'2005-09-01'); +---+ | rtBillingRate | +---+ | 8.00 | +---+ CORRECT! mysql call test_rate (1,37,'2005-10-10'); +---+ | rtBillingRate | +---+ | 25.00 | +---+ CORRECT! mysql call test_rate (1,NULL,'2005-10-10'); +---+ | rtBillingRate | +---+ | 0.00 | +---+ 1 row in set (0.01 sec) WRONG! This should have returned 8.00. When I run this query by itself (outside the procedure) I get the correct result: (notice I'm plugging in EMPID, BDID, and CURRENTDATE parameters) SELECT COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate) AS rtBillingRate FROM Rates rt1 LEFT JOIN Rates rt2 ON (rt2.empID=1 and rt2.chgID IS NULL and rt2.bdID IS NULL and rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID IS NULL and rtStartDate = '2005-10-10')) LEFT JOIN Rates rt3 ON (rt3.empID=1 and rt3.chgID IS NULL and rt3.bdID=NULL and rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates WHERE empID=1 and chgID IS NULL and bdID=NULL and rtStartDate = '2005-10-10')) WHERE rt1.empID IS NULL and rt1.chgID IS NULL; +---+ | rtBillingRate | +---+ | 8.00 | +---+ 1 row in set (0.00 sec) CORRECT! What's going wrong in the stored procedure? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
