Skip navigation links

MySQL Forums :: Stored Procedures :: conditional statements in stored procedure


Advanced Search

conditional statements in stored procedure
Posted by: David Gibbs ()
Date: July 06, 2013 08:59PM

I need some help with this concept that I'm trying to apply to MYSQL. I've used it in sql server 2008. I am trying to send a value through that will allow me to insert, update, or delete on a table based on this value. I am receiving a #1064 error message at ' ' at line 18. Any help greatly appreciated. Here is below:
DROP PROCEDURE IF EXISTS prcInserttblMovies;
CREATE PROCEDURE prcInserttblMovies (

IN_pvarRating varchar(5),
IN_pvarTitle varchar(255),
IN_pvarYear varchar(50),
IN_pintGenreSystemId int(11),
IN_pintLoanedOut int(11),
IN_pinttblBorrowerSystemId int(11),
IN_pvarDescription varchar(255),
IN_pintUserId int,
IN_pintMoviesSystemId int,
IN_paction varchar(10)
)
BEGIN
IF (IN_paction = I)
INSERT INTO tblMovies (varRating, varTitle, varYear, intGenreSystemId, intLoanedOut, intBorrowersSystemId, varDescription, intUserSystemId)
VALUES (IN_pvarRating, IN_pvarTitle, IN_pvarYear, IN_pintGenreSystemId, IN_pintLoanedOut, IN_pinttblBorrowerSystemId, IN_pvarDescription, IN_pintUserId);
SELECT MAX(tblMovies.intMoviesSystemId) into @RecordID FROM tblMovies LIMIT 1;
INSERT INTO tblMoviesHistory (intMoviesSystemId, varRating, varTitle, varYear, intGenreSystemId, intLoanedOut, intBorrowersSystemId, varDescription, intUserSystemId, varActionTaken)
SELECT @RecordID as intMoviesSystemId, varRating, varTitle, varYear, intGenreSystemId, intLoanedOut, intBorrowersSystemId, varDescription, intUserSystemId, 'Inserted'
FROM tblMovies WHERE tblMovies.intMoviesSystemId = @RecordID;
ELSE (IN_paction = U)
UPDATE tblMovies
SET varRating = IN_pvarRating,
varTitle = IN_pvarTitle,
varYear = IN_pvarYear,
intGenreSystemId = IN_pintGenreSystemId,
intLoadnedOut = IN_pintLoanedOut,
inttblBorrowerSystemId = IN_pinttblBorrowerSystemId,
varDescription = IN_pvarDescription,
intUserSystemId = IN_pintUserId
WHERE intMoviesSystemId = IN_pintMoviesSystemId;
END IF;
END;

Options: ReplyQuote


Subject Views Written By Posted
conditional statements in stored procedure 984 David Gibbs 07/06/2013 08:59PM
Re: conditional statements in stored procedure 321 Peter Brawley 07/07/2013 05:04AM
Re: conditional statements in stored procedure 303 Rick James 07/07/2013 04:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.