Stored Proc - Error #1064 near 'DECLARE ArtistId CHAR(36);

Dev Shed Forums Sponsor:

September 23rd, 2012, 11:52 AM

zemon1

Contributing User

Join Date: Dec 2008

Posts: 76

Time spent in forums: 1 Day 4 h 40 m 16 sec
Reputation Power: 25

Stored Proc - Error #1064 near 'DECLARE ArtistId CHAR(36); --SOLVED

Hello everyone,

I'm creating a store procedure to do a laborious insert for me so that my program code doesn't become ugly from a bunch of SQL calls if I can help it.

I have isolated the problem by moving around the location of the DECLARE statements and it always lands on them. I've been Googling around and cant find anything useful.

Here is the error im getting:
#1064 - 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 'DECLARE ArtistId CHAR(36); DECLARE AlbumId CHAR(36); INSERT INTO `PerlJ' at line 37

Here is my code:

Code:


DELIMITER //
CREATE PROCEDURE databaseInsert(
	IN SongName 		VARCHAR(255) ,
	IN AlbumName 		VARCHAR(255) ,
	IN ArtistName		VARCHAR(255) ,
	IN URL				VARCHAR(255) ,
	-- Now optional args
	IN SongMbId			CHAR(36) ,
	IN AlbumMbId		CHAR(36) ,
	IN ArtistMbId		CHAR(36) ,
	IN FileType			VARCHAR(255) ,
	IN TrackNum			INT(5) ,
	IN BitRate			INT(5) ,
	IN Bpm				INT(5) ,
	IN Tags				VARCHAR(255) ,
	IN Genre			VARCHAR(255) ,
	IN AlbumYear		DATE ,
	IN TotalTracks		INT(5) ,
	OUT ReturnCode		INT(3))
  BEGIN            
	IF SongName = "" THEN SET ReturnCode = -1;
	IF AlbumName = "" THEN SET ReturnCode = -2;
        IF ArtistName = "" THEN SET ReturnCode = -3;
	IF URL = "" THEN SET ReturnCode = -4;
	
	IF SongMbId = "" THEN SET SongMbId = Null;
	IF AlbumMbId = "" THEN SET AlbumMbId = Null;
	IF ArtistMbId = "" THEN SET ArtistMbId = Null;
	IF FileType = "" THEN SET FileType = Null;
	IF TrackNum = "" THEN SET TrackNum = Null;
	IF BitRate = "" THEN SET BitRate = Null;
	IF Bpm = "" THEN SET Bpm = Null;
	IF Tags = "" THEN SET Tags = Null;
	IF Genre = "" THEN SET Genre = Null;
	IF AlbumYear = "" THEN SET AlbumYear =  Null;
	IF TotalTracks = "" THEN SET TotalTracks = Null;
	
	DECLARE ArtistId	CHAR(36);
	DECLARE AlbumId		CHAR(36);
	
	INSERT INTO  `PerlJams1`.`Artist` (
		`ArtistId` ,
		`MbId` ,
		`ArtistName`)
	VALUES (
		UUID( ) ,
		ArtistMbId ,
		ArtistName);
	
	
	
	SELECT `ArtistId` INTO ArtistId
		FROM `PerlJams1`.`Artist` 
		WHERE `MbId` = ArtistMbId;
		
	INSERT INTO  `PerlJams1`.`Album` (
		`AlbumId` ,
		`MbId` ,
		`ArtistId` ,
		`AlbumName` ,
		`AlbumYear` ,
		`TotalTracks`)
	VALUES (
		UUID( ) ,  
		AlbumMbId ,  
		ArtistId ,  
		AlbumName , 
		AlbumYear , 
		TotalTracks);
	
	
	
	SELECT `AlbumId` INTO AlbumId
		FROM `PerlJams1`.`AlbumId` 
		WHERE `MbId` = AlbumMbId;	
	
	INSERT INTO  `PerlJams1`.`Song` (
		`SongId` ,
		`MbId` ,
		`AlbumId` ,
		`Title` ,
		`TrackNum` ,
		`BitRate` ,
		`Bpm` ,
		`URL` ,
		`FileType` ,
		`Genre` ,
		`Tags`)
	VALUES (
		UUID( ) , 
		SongMbId ,  
		AlbumId ,  
		SongName , 
		TrackNum , 
		BitRate , 
		Bpm ,  
		URL ,  
		FileType , 
		Genre , 
		Tags);
		
	 SET ReturnCode = 0;	
	
  END //
DELIMITER ;

Last edited by zemon1 : September 23rd, 2012 at 01:42 PM.

September 23rd, 2012, 01:27 PM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 27 m 6 sec
Reputation Power: 390

Declarations must be placed before all other statements within a begin end block.

Also, your if statements need an end if

Code:

if  SongName = '' then
    set ReturnCode = -1;
end if;

September 23rd, 2012, 01:41 PM

zemon1

Contributing User

Join Date: Dec 2008

Posts: 76

Time spent in forums: 1 Day 4 h 40 m 16 sec
Reputation Power: 25

Thank you

Worked Like a charm! Thank you very much!

Stored Proc - Error #1064 near 'DECLARE ArtistId CHAR(36);

Developer Shed Advertisers and Affiliates