Wednesday, August 15, 2012

Creating a Sproc/Function in your mysql db

I kept getting  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line x.

I was trying to run the sql to create the sproc via a txt/sql file and then via terminal do a
$mysql -u root -p < sproc_create_filename.sql

Turns out, you need to have a DELIMITER when doing this. This is how I got it to finally work..


DELIMITER //
CREATE DEFINER = `username`@`%` PROCEDURE `sp_get_mainpage_data`(lpIntCustomerId INT)
BEGIN
-- , lpIntLocationId INT, lpStrLoanOfficerCode VARCHAR(48), lpIntDecision TINYINT, lpDateInitialScoreDate DATETIME,  lpDateEndScoreDate DATETIME

.
.
.
. and (shi.id IS NOT NULL OR (sbjasmt.repeated = 'y' OR sbjasmt.status = 'incomplete'));
END//

Just adding the DELIMITER // and END// sorted out the issue.

No comments:

Post a Comment

Popular Posts