#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    76
    Rep Power
    26

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    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;
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    76
    Rep Power
    26

    Thank you


    Worked Like a charm! Thank you very much!

IMN logo majestic logo threadwatch logo seochat tools logo