error 1064 stored procedure using navicat

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • josequinonesii
    New Member
    • Aug 2009
    • 7

    error 1064 stored procedure using navicat

    I'm attempting to create/update a stored procedure. it's within the navicat environment but after looking and reading various forums the environment that I'm using to create it doesn't seem to be an issue. is there anything you can see that I'm not?

    i'm guessing it's something small my eyes have continued to overlook

    Code:
    CREATE PROCEDURE `communityFloorplans`(in generalCommunity varchar(55), in priceRange varchar(10), in minBed varchar(2), in minBath varchar(2))
    begin
    
    set @minimumBed   = 2;
    set @minimumBath  = 2;
    set @minimumPrice  = 150000;
    set @maximumPrice = 160000;
    
    select
    	c.communityID, c.communityName, min(l.price) 
    from 
    	communities c, listings l
    where
    	l.communityID = c.communityID
    and
    	case  generalCommunity when 'Lakeland' 
    		then	c.addrCity = 'Lakeland' OR c.addrCity= 'Bartow' OR c.addrCity = 'Mulberry'
    	  when 'Winter Haven' 
    		then	c.addrCity = 'Lake Hamilton' OR c.addrCity = 'Winter Haven' OR c.addrCity = 'Davenport'
    	  when 'Tampa'
    		then	c.addrCity = 'Wimauma' OR c.addrCity = 'Wesley Chapel' OR c.addrCity = 'Tampa'
    	  when 'Orlando' 
    		then	c.addrCity = 'Orlando'
    	  when 'Leesburg' 
    		then	c.addrCity = 'Leesburg'
    and
    	l.beds >= @minimumBed
    and
    	l.baths >= @minimumBath
    and
    	l.price between @minimumPrice and @maximumPrice
     group by 
     	c.communityid, c.communityname
    order by
    	c.communityname
    end
  • josequinonesii
    New Member
    • Aug 2009
    • 7

    #2
    i added an END to the Case section
    # case generalCommunit y when 'Lakeland'
    # then c.addrCity = 'Lakeland' OR c.addrCity= 'Bartow' OR c.addrCity = 'Mulberry'
    # when 'Winter Haven'
    # then c.addrCity = 'Lake Hamilton' OR c.addrCity = 'Winter Haven' OR c.addrCity = 'Davenport'
    # when 'Tampa'
    # then c.addrCity = 'Wimauma' OR c.addrCity = 'Wesley Chapel' OR c.addrCity = 'Tampa'
    # when 'Orlando'
    # then c.addrCity = 'Orlando'
    # when 'Leesburg'
    # then c.addrCity = 'Leesburg'
    # END

    I also added a @ to generalCommunit y with an assigned value 'Lakeland' just to see if I can save it. Now I can save the sp but when I tried to assign the input parameters as I would expect dynamic values to populate the input params I get the error all over again. <scratch my head>

    Comment

    Working...