Suhail Gupta Suhail Gupta - 4 months ago 11
SQL Question

What is the need to refer the table attribute with the table name while assigning a variable?

In the following stored procedure, I execute a statement:

select @subscriptionId := SubscriptionId from subscriptioninfo
where subscriptioninfo.UserId = userId;


But if I change the statement to

select @subscriptionId := SubscriptionId from subscriptioninfo
where UserId = userId;


It returns multiple results though it should return only single result.

What difference does
subscriptioninfo.UserId
create here, when it is obvious that the table named
subscriptioninfo
is being referred.

DELIMITER $$

DROP PROCEDURE IF EXISTS `portaldb`.`update_user_credit_limit`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user_credit_limit`(userId int,planId int, newCreditLimit int)
begin
if newCreditLimit is not null then
/**
* Get the subscription id required to update the max credit allocated into the
* planallocation table
* The following query creates a local variable @subscriptionId set to the user's
* subscription id
*/
select @subscriptionId := SubscriptionId from subscriptioninfo where subscriptioninfo.UserId = userId;
if @subscriptionId is not null then
/**
* Now update the user's MaxCreditPulseAllocated attribute
*/
update planallocation set MaxCreditPulseAllocated = newCreditLimit where
CurrentPlanId = planId and SubscriptionId = @subscriptionId;
end if;
end if;
end$$

DELIMITER ;

Answer

The reason is because your variable name is the same as the table column (variable and column names are case-insensitive in MySQL). When you write the name without a table prefix, it interprets it as the variable, not the column. So WHERE UserId = userId is always true because you're comparing the variable to itself, so it returns all columns in the table.

Change your procedure to use a different name for the parameter and you won't have this problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user_credit_limit`(p_userId int,planId int, newCreditLimit int)
begin
    if newCreditLimit is not null then 
        /**
         * Get the subscription id required to update the max credit allocated into the 
         * planallocation table
         * The following query creates a local variable @subscriptionId set to the user's
         * subscription id
         */
        select @subscriptionId := SubscriptionId from subscriptioninfo where UserId = p_userId;
        if @subscriptionId is not null then
            /**
             * Now update the user's MaxCreditPulseAllocated attribute
             */
            update planallocation set MaxCreditPulseAllocated = newCreditLimit where
                CurrentPlanId = planId and SubscriptionId = @subscriptionId;
        end if;
    end if;
end$$

BTW, you don't need to do two queries, you can do a single query with a JOIN.

UPDATE planallocation AS p
JOIN subscriptioninfo AS s ON p.SubscriptionId = s.SubscriptionId
SET p.MaxCreditPulseAllocated = newCreditLimit
WHERE p.CurrentPlanId = planId
AND s.UserId = p_userId