Results 1 to 3 of 3

Thread: Store Procedure

  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Exclamation Unanswered: Store Procedure

    Code:
    delimiter //
    CREATE PROCEDURE display_templates (IN _name VARCHAR(100),IN _category INT(1), IN _start INT(4), IN _limit INT(4))
    BEGIN
    
    DECLARE str_query VARCHAR(1000) DEFAULT '';
    DECLARE str_cond VARCHAR(255) DEFAULT '';
    DECLARE str_limit VARCHAR(50) DEFAULT ' LIMIT ?,?';
    
    DECLARE CATEGORY_OWN_TEMPLATES INT DEFAULT 0;
    DECLARE CATEGORY_USER_TEMPLATES INT DEFAULT 1;
    
    IF (_category = CATEGORY_OWN_TEMPLATES)
    	SET str_cond = CONCAT(' AND frm.authorName=''', _name, '''');
    END IF
    
    SET str_query=
    	'SELECT
    		fbt.authorName as author_name
    		, fbt.formName as form_name
     	FROM form_template fbt
    		INNER JOIN form frm ON frm.appType=fbt.appType
    		AND frm.formType=fbt.formType
    		AND frm.authorName=fbt.authorName
    		AND frm.formName=fbt.formName
        ';
    SET str_limit = ' LIMIT ?,?';
    SET str_query = CONCAT (str_query, str_cond, str_limit);
    PREPARE stmt FROM str_query;
    EXECUTE stmt USING _start, _limit;
    DEALLOCATE PREPARE stmt;
    END;
    //
    delimiter ;
    http://pastebin.com/m2fbda726 (better view)

    My error was:
    Script line: 2 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 'SET str_cond = CONCAT(' AND frm.authorName=''', _name, '''');
    END IF

    SET str' at line 12

    Any idea what is my error about?
    Thanks

  2. #2
    Join Date
    Mar 2008
    Posts
    3
    - missing THEN for IF
    - should be ELSEIF instead of ELSE IF
    - EXECUTE stmt USING _start, _limit; << should be using USER DEFINE variable rather than local variable

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    thread close

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •