Convert procedure from MSSQL to MySQL

[Philippe -]

I edited my post since i got no aswers on this board (about a problem) and i was able to make a succesfull Query. So here it is for anyone who is interested:

DELIMITER $$

SET SQL_MODE = ANSI_QUOTES$$

DROP PROCEDURE IF EXISTS AddEdge$$

CREATE PROCEDURE AddEdge (
	IN v_StartVertexId INT UNSIGNED,
	IN v_EndVertexId INT UNSIGNED)
AddEdge_BLOCK: BEGIN
	DECLARE v_Id INT UNSIGNED;
	IF EXISTS (SELECT Id
		FROM edges
		WHERE StartVertex = v_StartVertexId
			AND EndVertex = v_EndVertexId
			AND Hops = 0)
		THEN
			LEAVE AddEdge_BLOCK;
	END IF;

	IF v_StartVertexId = v_EndVertexId
		OR EXISTS (SELECT Id 
								FROM edges 
								WHERE StartVertex = v_EndVertexId 
								AND EndVertex = v_StartVertexId)
	THEN
		-- RAISERROR ('Attempt to create a circular relation detected!', 16, 1); -- MSSQL statement
		-- MySQL workaround (not implemented): http://blogs.oracle.com/svetasmirnova/entry/how_to_raise_error_in
		LEAVE AddEdge_BLOCK;
	END IF;

	INSERT INTO edges (
		StartVertex,
		EndVertex,
		Hops)
	VALUES (
		v_StartVertexId,
		v_EndVertexId,
		0);

	SET v_Id := LAST_INSERT_ID();
	
	UPDATE edges
		SET EntryEdgeId = v_Id
			, ExitEdgeId = v_Id
			, DirectEdgeId = v_Id 
		WHERE Id = v_Id;

	-- step 1: A's incoming edges to B
	INSERT INTO edges (
		EntryEdgeId,
		DirectEdgeId,
		ExitEdgeId,
		StartVertex,
		EndVertex,
		Hops) 
		SELECT Id
			, v_Id
			, v_Id
			, StartVertex 
			, v_EndVertexId
			, Hops + 1
		FROM edges
		WHERE EndVertex = v_StartVertexId;

	-- step 2: A to B's outgoing edges
	INSERT INTO edges (
		EntryEdgeId,
		DirectEdgeId,
		ExitEdgeId,
		StartVertex,
		EndVertex,
		Hops) 
		SELECT v_Id
			, v_Id
			, Id
			, v_StartVertexId 
			, EndVertex
			, Hops + 1
		FROM edges
		WHERE StartVertex = v_EndVertexId;

	-- step 3: A’s incoming edges to end vertex of B's outgoing edges
	INSERT INTO Edge (
		EntryEdgeId,
		DirectEdgeId,
		ExitEdgeId,
		StartVertex,
		EndVertex,
		Hops)
		SELECT A.Id
			, v_Id
			, B.Id
			, A.StartVertex 
			, B.EndVertex
			, A.Hops + B.Hops + 1
		FROM Edge A
			CROSS JOIN Edge B
		WHERE A.EndVertex = v_StartVertexId
			AND B.StartVertex = v_EndVertexId;
END AddEdge_BLOCK$$
DELIMITER ;

Edited 4 time(s). Last edit at 12/07/2011 11:01AM by Philippe -.