Syntax error on IF statement using stored procedure converted from MS-SQL to MySQL

Question

I am converting a stored procedure from MS-SQL to MySQL. It is based around Directed Acyclic Graphs but I am getting a syntax error.

The original MS-SQL script is in Listing 2 on the following page: http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o

The error I get is: #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 varId int; INSERT INTO edges ( startVertex, en' at line 36

The MySQL code:

DELIMITER //
CREATE PROCEDURE AddEdge(
IN iStartVertexId varchar(36),
IN iEndVertexId varchar(36),
IN iSource varchar(150)
)

MAIN_BLOCK: BEGIN

DECLARE counter int default 0;
SET counter = (SELECT id 
   FROM edges 
   WHERE startVertex = iStartVertexId 
     AND endVertex = iEndVertexId 
     AND hops = 0);
IF counter > 0 THEN
   BEGIN
      LEAVE MAIN_BLOCK;
   END;
END IF;

SET counter = 0;
SET counter = (SELECT Id 
                     FROM edges
                     WHERE StartVertex = @EndVertexId 
                       AND EndVertex = @StartVertexId);

IF iStartVertexId = iEndVertexId 
      OR counter > 0
THEN
BEGIN

     LEAVE MAIN_BLOCK;
END;
END IF;


DECLARE varId int;

INSERT INTO edges (
     startVertex,
     endVertex,
     hops,
     source)
  VALUES (
     iStartVertexId,
     iEndVertexId,
     0,
     iSource);

SELECT varId = LAST_INSERT_ID();
UPDATE edges
  SET entryEdgeId = varId
    , exitEdgeId = varId
    , directEdgeId = varId 
  WHERE id = varId;

-- step 1: A's incoming edges to B
INSERT INTO edges (
     entryEdgeId,
     directEdgeId,
     exitEdgeId,
     startVertex,
     endVertex,
     hops,
     source) 
  SELECT id
     , varId
     , varId
     , startVertex 
     , iEndVertexId
     , hops + 1
     , iSource
  FROM edges
  WHERE endVertex = iStartVertexId;

-- step 2: A to B's outgoing edges
INSERT INTO edges (
     entryEdgeId,
     directEdgeId,
     exitEdgeId,
     startVertex,
     endVertex,
     hops,
     source) 
  SELECT varId
     , varId
     , id
     , iStartVertexId 
     , endVertex
     , hops + 1
     , iSource
  FROM edges
  WHERE startVertex = iEndVertexId;

-- step 3: A’s incoming edges to end vertex of B's outgoing edges
INSERT INTO edges (
     entryEdgeId,
     directEdgeId,
     exitEdgeId,
     startVertex,
     endVertex,
     hops,
     source)
  SELECT A.id
     , varId
     , B.id
     , A.startVertex 
     , B.endVertex
     , A.hops + B.hops + 1
     , iSource
  FROM edges A
     CROSS JOIN edges B
  WHERE A.endVertex = iStartVertexId
    AND B.startVertex = iEndVertexId;

END //
DELIMITER ;

This works fine without the IF statements so I think my syntax is a bit wrong. Any ideas?

You might want to consider using triggers, rather than a SP: if defined correctly you will then be able to make changes directly to the underlying tables and have MySQL automatically maintain the DAG. — eggyal
– eggyal, Commented Aug 30, 2012 at 14:19

eggyal · Accepted Answer · 2012-08-30 14:16:26Z

1

As stated under DECLARE Syntax:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

answered Aug 30, 2012 at 14:16

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Pitt Over a year ago

Thanks again Eggyal :) More surprised that was the only error in my code although I suppose the syntax may be right but it doesn't mean it will work ;)

eggyal Over a year ago

@JamesPitt: To be honest, I haven't read your code - was just pointing out what caused the syntax error you mentioned :) For a more thorough review of your code, you should probably try Code Review.

Collectives™ on Stack Overflow

Syntax error on IF statement using stored procedure converted from MS-SQL to MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related