CODEWITHSUNDEEP
c#mysqlsqlstored-procedures
sfdc-neta
sfdc-neta

Reputation: 251

MySql Stored Procedure not working

I have created a mysql procedure but its not setting the value paid=1 in case of proper sql query execution:

PROCEDURE `PaidUser`(in IpId varchar(45),in UId int(20))
BEGIN

if(select count(*) as count from  users u inner join subscription s on u.ID=s.usersID inner join item i on i.id=s.itemID  where i.item_typeID !=6 and u.ID = UId having count >0)

then

update location_byhits set Paid=1 where  location_byhits.IpAddress =IpId;

END if;

end

Upvotes: 0

Views: 263

Answers (1)

GarethD
GarethD

Reputation: 69829

You are missing the CREATE from the start of your stored procedure, by adding this I was able to get your procedure working on SQL Fiddle (Note the 4th result set has one row of paid updated)

To test I created the following data:

CREATE TABLE location_byhits (Paid INT, IpAddress INT);
CREATE TABLE Users (ID INT);
CREATE TABLE Subscription(ItemID INT, UsersID INT);
CREATE TABLE Item (ID INT, Item_TypeID INT);
//
INSERT location_byhits VALUES (0, '192.168.0.0'), (0, '192.168.0.1');
INSERT Users VALUES (1), (2);
INSERT Subscription (1, 1), (2, 2);
INSERT Item (1, 1), (2, 6);

Then called the procedure 4 times with various parameters.

CALL PaidUser ('192.168.0.2', 1);
CALL PaidUser ('192.168.0.0', 3);
CALL PaidUser ('192.168.0.1', 2);
CALL PaidUser ('192.168.0.1', 1);
  • The first should not update as there is no IP Match.
  • The Second should not update as there is no user match.
  • The third should not update because although there is a user and an IP match, user 2 has no items associated that don't have an Item_TypeID of 6.
  • The fourth should update because there is both a user match and an ip match, and the user has associated items that have and item type id other than 6.

I would be inclined to rewrite your stored procedure as follows though:

CREATE PROCEDURE `PaidUser`(in IpId VARCHAR(45),in UId INT(20))
BEGIN

    UPDATE  location_byhits 
    SET     Paid = 1 
    WHERE   location_byhits.IpAddress = IpId
    AND     EXISTS
            (   SELECT  1
                FROM    Users u 
                        INNER JOIN Subscription s 
                            ON u.ID = s.usersID 
                        INNER JOIN Item i 
                            ON i.ID = s.ItemID  
                WHERE   i.Item_TypeID != 6 
                AND     u.ID = UId 
            );
END

If you do the check before the update, although it is very unlikely, it is not impossible that in between starting the check and doing the update the data is changed by another session. This could result in unexpected behaviour. If you do it all in a single statement you avoid concurrency issues.

Example on SQL Fiddle

Upvotes: 1

Related Questions