MySQL Forums
Forum List  »  Stored Procedures

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
3786
July 06, 2013 08:59PM


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.