CODEWITHSUNDEEP
mysql
user123
user123

Reputation: 850

Unable to pass parameter to stored procedure

My requirement to calculate sum of day, month and year amounts. I written the stored procedure as below.

DELIMITER $$

CREATE DEFINER=`ntc`@`%` PROCEDURE `AgentRank`(IN Agentid int)

BEGIN
    select (select  @DayAmount := sum(AmountRecevied) as Totoalamountperday from 
        collection_master  
        where  AgentID=Agentid and  day(Date_Time)= day(CURRENT_DATE()) 
        group by AgentID
    ) as Dayamount,

    (select  @MonthAmount :=sum(AmountRecevied) as Totoalamountperday  from 
        collection_master
        where  AgentID=Agentid and date_time between DATE_FORMAT(NOW() ,'%Y-%m-01') and LAST_DAY(now() - interval 0 month )
        group by AgentID
    ) as monthamount,

    (select   @YearAmount := sum(AmountRecevied) as Totoalamountpermonth  from 
        collection_master  
        where AgentID=Agentid  and year(Date_Time) =YEAR(CURRENT_DATE())
        group by AgentID
    ) as yearamount,

    (select @Position := @Position + 1 AS Rank  from 
        collection_master ,(SELECT @Position := 0) r
        where AgentID=Agentid
        group by AgentID
    ) as position;

END

When I am executing the stored procedure I am getting below outputenter image description here

When I am executing in individual query of store procedure I am getting correct output as belowenter image description here

Please check it once and where I am wrong?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This is the first few lines of the stored procedure:

CREATE DEFINER=`ntc`@`%` PROCEDURE `AgentRank`(IN Agentid int)
BEGIN
    select (select  @DayAmount := sum(AmountRecevied) as Totoalamountperday
            from collection_master  
            where  AgentID=Agentid and  day(Date_Time)= day(CURRENT_DATE()) 
-------------------^
            group by AgentID
           ) as Dayamount,

The pointed-to expression is a tautology. That is, it is comparing the column AgentId to itself. The problem is that your parameter has the same name as the column, a bad, bad, bad, bad idea. Here is a fix:

CREATE DEFINER=`ntc`@`%` PROCEDURE `AgentRank`(IN v_Agentid int)
BEGIN
    select (select  @DayAmount := sum(AmountRecevied) as Totoalamountperday
            from collection_master  
            where AgentID = v_Agentid and day(Date_Time)= day(CURRENT_DATE()) 
           ) as Dayamount,

Also note that you do not need the aggregation. The subquery has to return zero or one rows (or you get an error). You are selecting only one AgentId, so remove the group by to prevent misinterpretation.

Upvotes: 1

Related Questions