List:General Discussion« Previous MessageNext Message »
From:Josh Date:March 10 2006 2:36pm
Subject:Re: Stored Procedure Problem
View as plain text  
Peter, nice catch.  Changing the parameter names did
the trick.  Thanks.

--- Peter Brawley <peter.brawley@stripped> 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 | 0000-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/mysql?unsub=1
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
Stored Procedure ProblemJosh10 Mar
  • Re: Stored Procedure ProblemPeter Brawley10 Mar
    • Re: Stored Procedure ProblemJosh10 Mar