Creating stored procedure from install file

I had the same problem you had. Eliminate the "DELIMITER $", it is not necesarry when you create the SP from PHP.

For example this is how I do it:

• First I store the store procedure in a file ending with .sql, since some SPs can be quite large:

  Example filename: sp_mystoredprocedure1.sql

  CREATE  PROCEDURE sp_mystoredprocedure1( )
  BEGIN
   SELECT node.nid AS nid,
     node.title AS node_title,
     node_revisions.teaser AS node_revisions_teaser,
     node_revisions.format AS node_revisions_format,
     node.created AS node_created,
     content_type_story.field_image_slideshow_fid AS field_image_slideshow_fid,
     files.filepath AS files_filepath,
     users.name AS author
   FROM node node
   LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
   LEFT JOIN content_type_story content_type_story ON node.vid = content_type_story.vid
   LEFT JOIN files files ON content_type_story.field_image_slideshow_fid = files.fid
   LEFT JOIN users users ON node.uid = users.uid
   WHERE (node.status <> 0) AND (node.type in ('story')) AND content_type_story.field_slideshow_slideshow_value = 1
     ORDER BY node.created DESC LIMIT 4;
  END
  
  # end procedure -- this is just a comment, you can delete it
  

• Then from your module's PHP code you can read the *.sql file and successfully call the SQL query above (in this case a stored procedure). Demonstration:
  

  # The module's installation hook
  function yourmodulename_enable() {
  	
  	$module_path = drupal_get_path('module', 'yourmodulename');
  	$stored_procedures = array('sp_mystoredprocedure1.sql', 'sp_mystoredprocedure2.sql', 'sp_mystoredprocedure3.sql', ...);
  	
  	foreach ($stored_procedures as $file) {
  		$sp_string = file_get_contents("$module_path/stored_procedures/$file");
  		$result = db_query($sp_string);
  	}
  	
  	drupal_set_message("Database stored procedures created.", 'status');
  }
  
  

Once you run that SQL code from PHP, MySQL automatically adds the "DELIMITER $" when it "stores" the procedure. You can verify this by opening the procedure for editing using a MySQL command or GUI editor like MySQL Query Editor.