Can't create stored procedure with parameters

Question

I'm trying to create a stored procedure with the in parameters first date, last date and country

This is the normal query witch works just fine.

SELECT CAR_TYPE.NAME, CAR_TYPE.TYPE, CAR.DAY_PRICE, CAR.REGISTRATION_NUMBER, AGENCY.NAME, BRANCH.NAME
FROM CAR
JOIN CAR_TYPE ON CAR_TYPE.ID = CAR.CAR_TYPE_ID
JOIN BRANCH ON BRANCH.ID = CAR.BRANCH_ID    
JOIN BRANCH_ADDRESS ON BRANCH_ADDRESS.BRANCH_ID = BRANCH.ID
JOIN AGENCY ON AGENCY.ID = BRANCH.ID`  
WHERE CAR.ID NOT IN
(SELECT BOOKING.CAR_ID FROM BOOKING WHERE BOOKING.PICKUP_DATE < '2013-01-01' AND BOOKING.RETURN_DATE > '2013-12-12')
AND BRANCH_ADDRESS.CITY_NAME = 'Stockholm'
end

Which database are you using? There are slight differences between them that might matter. — Dan Pichelman, May 30 '13 at 01:38
Take a look at this: http://stackoverflow.com/questions/5039324/creating-a-procedure-in-mysql-with-parameters — sgeddes, May 30 '13 at 01:42
I'm new at this, so i only learnt the basis stuff. my question is how is et possible to put this in a stored procedure, were i can type in the parameters. CALL AVAILIBLECAR('2013-01-01','2013-12-30','STOCKHOLM') — Christian Stenholt Øelund, May 30 '13 at 01:46
Welcome to SO! As the other comments have suggested, it's best to include a really specific question in the body of your question. — alexwhan, May 30 '13 at 01:53

Charles Bretana · Answer 1 · 2013-05-30T01:53:16.857

I can't make out what you are doing from your question, as it is not formatted well.

I have reformatted it so that it looks like a proper query, and made some changes to improve readability.

SELECT t.NAME, t.TYPE, c.DAY_PRICE, 
      c.REGISTRATION_NUMBER, a.NAME, b.NAME`

FROM CAR c
    JOIN CAR_TYPE t ON t.ID = c.CAR_TYPE_ID
    JOIN BRANCH b ON b.ID = c.BRANCH_ID
    JOIN BRANCH_ADDRESS ba ON ba.BRANCH_ID = b.ID
    JOIN AGENCY a ON a.ID = b.ID

WHERE ba.CITY_NAME = 'Stockholm'
  And c.ID NOT IN
      (SELECT CAR_ID 
       FROM BOOKING 
       WHERE PICKUP_DATE < '2013-01-01' 
       AND RETURN_DATE > '2013-12-12')

if this is SQL server, then the statement to cerate a basic (without error handling) stored proc would be

Create Procedure dbo.FetchCarData -- or whatever you want to name it
@firstDate DateTime,
@lastDate DateTime,
@country varChar(50)    
As
  SELECT t.NAME, t.TYPE, c.DAY_PRICE, 
      c.REGISTRATION_NUMBER, a.NAME, b.NAME`

  FROM CAR c
    JOIN CAR_TYPE t ON t.ID = c.CAR_TYPE_ID
    JOIN BRANCH b ON b.ID = c.BRANCH_ID
    JOIN BRANCH_ADDRESS ba ON ba.BRANCH_ID = b.ID
    JOIN AGENCY a ON a.ID = b.ID`

  WHERE ba.CITY_NAME = @country 
    And c.ID NOT IN
      (SELECT CAR_ID 
       FROM BOOKING 
       WHERE PICKUP_DATE < @firstDate 
       AND RETURN_DATE > @lastDate )

in MySQL I think it would look like this...

Create Procedure FetchCarData( -- or whatever you want to name it
  @firstDate DateTime,
  @lastDate DateTime,
  @country varChar(50))   
Begin
   SELECT t.NAME, t.TYPE, c.DAY_PRICE, 
      c.REGISTRATION_NUMBER, a.NAME, b.NAME`

   FROM CAR c
    JOIN CAR_TYPE t ON t.ID = c.CAR_TYPE_ID
    JOIN BRANCH b ON b.ID = c.BRANCH_ID
    JOIN BRANCH_ADDRESS ba ON ba.BRANCH_ID = b.ID
    JOIN AGENCY a ON a.ID = b.ID`

   WHERE ba.CITY_NAME = @country 
     And c.ID NOT IN
       (SELECT CAR_ID 
        FROM BOOKING 
        WHERE PICKUP_DATE < @firstDate 
        AND RETURN_DATE > @lastDate )   
End

Still wont work, i've tried something like this, it will create the trigger, but won't give me any outputs DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `AVAILIBLECAR2`(in Datefirst DATE,in Datelast DATE,in Country varchar(11)) SELECT CAR_TYPE.NAME, CAR_TYPE.TYPE, CAR.DAY_PRICE, CAR.REGISTRATION_NUMBER, AGENCY.NAME AS AGENCY_NAME, BRANCH.NAME AS — Christian Stenholt Øelund, May 30 '13 at 02:18
BRANCH_NAME FROM CAR JOIN CAR_TYPE ON CAR_TYPE.ID = CAR.CAR_TYPE_ID JOIN BRANCH ON BRANCH.ID = CAR.BRANCH_ID JOIN BRANCH_ADDRESS ON BRANCH_ADDRESS.BRANCH_ID = BRANCH.ID JOIN AGENCY ON AGENCY.ID = BRANCH.ID WHERE CAR.ID NOT IN (SELECT BOOKING.CAR_ID FROM BOOKING WHERE BOOKING.PICKUP_DATE < Datefirst AND BOOKING.RETURN_DATE > Datelast) AND BRANCH_ADDRESS.CITY_NAME = Country;; DELIMITER ; — Christian Stenholt Øelund, May 30 '13 at 02:18

Can't create stored procedure with parameters

1 Answers1