CODEWITHSUNDEEP
mysqlstored-procedures
TSCAmerica.com
TSCAmerica.com

Reputation: 5377

Mysql Stored procedure returns some weird results

When I run the following select Query in SQl

SELECT Count(*) 
FROM   workordercurrent 
WHERE  office_id = 1 
       AND ( ( scheduleddate = '2018-11-01' ) 
              OR ( schedulestopdate = '2018-11-01' ) 
              OR ( scheduleddate = '0000-00-00' 
                   AND orderdate = '2018-11-01' ) ) 
       AND worktype <> 6

The query returns 694 as count which is right

When I write the same Query in the SQL Procedure with 2 input parameters 

office_id(int) and order_date (DATE)


BEGIN 
    SELECT Count(*) 
    FROM   workordercurrent 
    WHERE  office_id = office_id 
           AND ( ( scheduleddate = order_date ) 
                  OR ( schedulestopdate = order_date ) 
                  OR ( scheduleddate = '0000-00-00' 
                       AND orderdate = order_date ) ) 
           AND worktype <> 6; 
END 

It returns the count as 3260

What is the problem here as both queries exactly same. Here is how I am running the Stored procedure

enter image description here

Upvotes: 1

Views: 51

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You should avoid using Stored procedure's parameter name same as the columns/aliases used in your SP. WHERE office_id = office_id is behaving weird due to ambiguous name. MySQL is probably not able to resolve it as either a column name or parameter.

I normally prefix in_ or out_ or inout_ to param names; which also shows the type of param (for readability).

So you can rename the parameters to in_office_id and in_order_date instead.

Upvotes: 1

Related Questions