
DROP DATABASE IF EXISTS SpTest;
CREATE DATABASE SpTest;
USE SpTest;

CREATE TABLE Table1 (
	Table1Id INT AUTO_INCREMENT NOT NULL,
	Name VARCHAR(100) NOT NULL,
	PRIMARY KEY(Table1Id)
);

CREATE TABLE Table2 (
	Table2Id INT AUTO_INCREMENT NOT NULL,
	PRIMARY KEY(Table2Id)
);

CREATE TABLE Table3 (
	Table3Id INT AUTO_INCREMENT NOT NULL,
	Table1Id INT NOT NULL,
	Table2Id INT NOT NULL,
	PRIMARY KEY(Table3Id)
);

INSERT INTO Table1 (Table1Id, Name) VALUES (1, "Item1");
INSERT INTO Table1 (Table1Id, Name) VALUES (2, "Item2");
INSERT INTO Table2 (Table2Id) VALUES (1);
INSERT INTO Table3 (Table3Id, Table1Id, Table2Id) VALUES (1, 1, 1);
INSERT INTO Table3 (Table3Id, Table1Id, Table2Id) VALUES (2, 2, 1);


delimiter //


CREATE PROCEDURE GetT1Data
(
	Name VARCHAR(100),
	OUT Table1Id INT
)
BEGIN

	SELECT t1.Table1Id INTO Table1Id FROM Table1 t1 WHERE t1.Name LIKE Name;

	SELECT
		t3.Table2Id
	FROM
		Table3 t3
	WHERE
		t3.Table1Id = Table1Id;

END
//