473,399 Members | 3,302 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,399 software developers and data experts.

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

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE `communityFloorplans`(in generalCommunity varchar(55), in priceRange varchar(10), in minBed varchar(2), in minBath varchar(2))
  2. begin
  3.  
  4. set @minimumBed   = 2;
  5. set @minimumBath  = 2;
  6. set @minimumPrice  = 150000;
  7. set @maximumPrice = 160000;
  8.  
  9. select
  10.     c.communityID, c.communityName, min(l.price) 
  11. from 
  12.     communities c, listings l
  13. where
  14.     l.communityID = c.communityID
  15. and
  16.     case  generalCommunity when 'Lakeland' 
  17.         then    c.addrCity = 'Lakeland' OR c.addrCity= 'Bartow' OR c.addrCity = 'Mulberry'
  18.       when 'Winter Haven' 
  19.         then    c.addrCity = 'Lake Hamilton' OR c.addrCity = 'Winter Haven' OR c.addrCity = 'Davenport'
  20.       when 'Tampa'
  21.         then    c.addrCity = 'Wimauma' OR c.addrCity = 'Wesley Chapel' OR c.addrCity = 'Tampa'
  22.       when 'Orlando' 
  23.         then    c.addrCity = 'Orlando'
  24.       when 'Leesburg' 
  25.         then    c.addrCity = 'Leesburg'
  26. and
  27.     l.beds >= @minimumBed
  28. and
  29.     l.baths >= @minimumBath
  30. and
  31.     l.price between @minimumPrice and @maximumPrice
  32.  group by 
  33.      c.communityid, c.communityname
  34. order by
  35.     c.communityname
  36. end
  37.  
Aug 26 '09 #1
1 5584
i added an END to the Case section
# 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'
# END

I also added a @ to generalCommunity 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>
Aug 26 '09 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Ola Ogunneye | last post by:
--=_075912CF.F899D547 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Hello again all, I have created an account in the particular database that I was trying to...
0
by: Morten Gulbrandsen | last post by:
C:\mysql\bin>mysql -u elmasri -pnavathe company Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 to server version: 4.1.0-alpha-max-debug Type...
2
by: Horacio Escalada | last post by:
Hi there, I would like to know if I can pass XML data to a MYSQL stored procedure and then process the XML file to insert its values into a table. Something like the "SELECT ... FROM OPENXML "...
0
by: Rhino | last post by:
I've written several Java stored procedures now (DB2 V7.2) and I'd like to write down a few "best practices" for reference so that I will have them handy for future development. Would the...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
1
by: Gayatri | last post by:
i've a sql query as follows: SELECT tb1.col1,tb2.col1,tb3.col1 FROM tb1,tb2,tb3 where tb1.col1=tb2.col2 AND tb3.col3=tb2.col3 AND tb2.col4 BETWEEN 4 AND 5 AND tb2.col5 BETWEEN 6 AND 7; if I...
3
by: gomzi | last post by:
DELIMITER $$ DROP PROCEDURE IF EXISTS `tableselect` $$ CREATE PROCEDURE `tableselect`() BEGIN select * from table_a; END $$ DELIMITER ;
4
by: Harshpandya | last post by:
Hi I am new at ASP and MYSQL. I am Using Navicat to access MYSQL database. My Problem is when i create the foreign key in "Navicat" -it gives me this error. 1452-cannot add or update child...
2
by: kxyz | last post by:
Hello everyone, I need help with a stored procedure or two. My stored procedures are supposed to check if a certain record exists. If it does exist, then I select everything from that row, as...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.