CODEWITHSUNDEEP
mysqlsqlstored-procedures
sfdc-neta
sfdc-neta

Reputation: 251

Stored Procedure in mysql Not working

I am trying to insert a value in case not inserted else trying to update some of its field.There is only one variable used. Value is not inserting although ion calling the store procedure it shows one row inserted. Kindly help me , trying SP first time.

This is mine stored procedure 

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertLocation`(in IpAddress varchar(45))
BEGIN     
  if (SELECT count(*) as count 
      FROM mbuzzz.location_byhits 
      where IpAddress = IpAddress
      having count>0)    
  then     
      UPDATE location_byhits SET Hits=Hits+1 where  IpAddress=IpAddress;    
  else     
      insert into location_byhits(IpAddress,Date_current) 
      values (IpAddress,CURTIME());     
  End if ;    
end

Upvotes: 2

Views: 6096

Answers (1)

juergen d
juergen d

Reputation: 204904

Rename your input parameter to make it clear to the DB engine when you mean the parameter and when the column name.

where IpAddress = IpAddress

is always true since the engine thinks you compare a column to itself.

Try

delimiter |
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertLocation`(in IpAddrParam varchar(45))
BEGIN     
  if ((SELECT count(*) FROM mbuzzz.location_byhits where IpAddress = IpAddrParam)>0)    
  then     
      UPDATE location_byhits SET Hits=Hits+1 where IpAddress=IpAddrParam;    
  else     
      insert into location_byhits(IpAddress,Date_current) 
      values (IpAddrParam, CURTIME());     
  End if ;    
end
|
delimiter ;

Upvotes: 5

Related Questions