Probleem

We maken de stored procedures voor de tabellen niet aan met behulp van een visuele designer maar met behulp van SQL scripts.

Design

Vooraleer de stored procedures te maken gaan we na als die al bestaat. Indien dit het geval is, deleten we eerst de stored procedure.

Oplossing

USE Vos2;
-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationInsert;
DELIMITER //
CREATE PROCEDURE `OrganisationInsert`
(
	IN pName NVARCHAR (255) ,
	IN pStreet NVARCHAR (255) ,
	IN pPostalCode VARCHAR (20) ,
	IN pCity NVARCHAR (80) ,
	IN pLatitude DECIMAL(7, 5) ,
	IN pLongitude DECIMAL(8, 5) ,
	IN pMobile VARCHAR (25) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Organisation`
	(
		`Organisation`.`Name`,
		`Organisation`.`Street`,
		`Organisation`.`PostalCode`,
		`Organisation`.`City`,
		`Organisation`.`Latitude`,
		`Organisation`.`Longitude`,
		`Organisation`.`Mobile`
	)
	VALUES
	(
		pName,
		pStreet,
		pPostalCode,
		pCity,
		pLatitude,
		pLongitude,
		pMobile
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationUpdate;
DELIMITER //
CREATE PROCEDURE `OrganisationUpdate`
(
	pName NVARCHAR (255) ,
	pStreet NVARCHAR (255) ,
	pPostalCode VARCHAR (20) ,
	pCity NVARCHAR (80) ,
	pLatitude DECIMAL(7, 5) ,
	pLongitude DECIMAL(8, 5) ,
	pMobile VARCHAR (25) ,
	pId INT 
)
BEGIN
UPDATE `Organisation`
	SET
		`Name` = pName,
		`Street` = pStreet,
		`PostalCode` = pPostalCode,
		`City` = pCity,
		`Latitude` = pLatitude,
		`Longitude` = pLongitude,
		`Mobile` = pMobile
	WHERE `Organisation`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationDelete;
DELIMITER //
CREATE PROCEDURE `OrganisationDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Organisation`
	WHERE `Organisation`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectOne;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`Street`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Latitude`,
	`Organisation`.`Longitude`,
	`Organisation`.`Mobile`,
	`Organisation`.`Id`
 
FROM `Organisation`
	WHERE `Organisation`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectAll;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectAll`
(
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`
	FROM `Organisation`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByName Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectByName;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`Name` = pName
	ORDER BY `Organisation`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByStreet Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectByStreet;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectByStreet`
(
	 pStreet NVARCHAR (255) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`Street` = pStreet
	ORDER BY `Organisation`.`Street`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByCity Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectByCity;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectByCity`
(
	 pCity NVARCHAR (80) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`City` = pCity
	ORDER BY `Organisation`.`City`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectById;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeName Stored Procedure for table Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikeName;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikeName`
(
	pName NVARCHAR (255) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`
 
	FROM `Organisation`
	WHERE `Organisation`.`Name` like CONCAT(pName, '%')
	ORDER BY `Organisation`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeStreet Stored Procedure for table Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikeStreet;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikeStreet`
(
	pStreet NVARCHAR (255) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`
 
	FROM `Organisation`
	WHERE `Organisation`.`Street` like CONCAT(pStreet, '%')
	ORDER BY `Organisation`.`Street`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikePostalCode Stored Procedure for table Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikePostalCode;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikePostalCode`
(
	pPostalCode VARCHAR (20) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`
 
	FROM `Organisation`
	WHERE `Organisation`.`PostalCode` like CONCAT(pPostalCode, '%')
	ORDER BY `Organisation`.`PostalCode`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeCity Stored Procedure for table Organisation 
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikeCity;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikeCity`
(
	pCity NVARCHAR (80) 
)
BEGIN
SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`
 
	FROM `Organisation`
	WHERE `Organisation`.`City` like CONCAT(pCity, '%')
	ORDER BY `Organisation`.`City`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXName Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikeXName;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikeXName`
(
	pName NVARCHAR (255) 
)
BEGIN
	SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`Name` like CONCAT('%', pName, '%')

	ORDER BY `Organisation`.`Name` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXStreet Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikeXStreet;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikeXStreet`
(
	pStreet NVARCHAR (255) 
)
BEGIN
	SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`Street` like CONCAT('%', pStreet, '%')

	ORDER BY `Organisation`.`Street` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXCity Stored Procedure for table Organisation
-- 
DROP PROCEDURE IF EXISTS OrganisationSelectLikeXCity;
DELIMITER //
CREATE PROCEDURE `OrganisationSelectLikeXCity`
(
	pCity NVARCHAR (80) 
)
BEGIN
	SELECT `Organisation`.`Name`,
	`Organisation`.`PostalCode`,
	`Organisation`.`City`,
	`Organisation`.`Id`

	FROM `Organisation`
	WHERE `Organisation`.`City` like CONCAT('%', pCity, '%')

	ORDER BY `Organisation`.`City` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Log 
-- 
DROP PROCEDURE IF EXISTS LogInsert;
DELIMITER //
CREATE PROCEDURE `LogInsert`
(
	IN pUserName NVARCHAR (50) ,
	IN pEmail NVARCHAR (255) ,
	IN pRole NVARCHAR (50) ,
	IN pProcedureCode NVARCHAR (25) ,
	IN pProcedureTitle NVARCHAR (255) ,
	IN pStepTitle VARCHAR (255) ,
	IN pActionCode NVARCHAR (10) ,
	IN pCallNumber VARCHAR (25) ,
	IN pSendNumber VARCHAR (25) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Log`
	(
		`Log`.`UserName`,
		`Log`.`Email`,
		`Log`.`Role`,
		`Log`.`ProcedureCode`,
		`Log`.`ProcedureTitle`,
		`Log`.`StepTitle`,
		`Log`.`ActionCode`,
		`Log`.`CallNumber`,
		`Log`.`SendNumber`,
		`Log`.`InsertedOn`
	)
	VALUES
	(
		pUserName,
		pEmail,
		pRole,
		pProcedureCode,
		pProcedureTitle,
		pStepTitle,
		pActionCode,
		pCallNumber,
		pSendNumber,
		NOW()
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Log
-- 
DROP PROCEDURE IF EXISTS LogUpdate;
DELIMITER //
CREATE PROCEDURE `LogUpdate`
(
	pUserName NVARCHAR (50) ,
	pEmail NVARCHAR (255) ,
	pRole NVARCHAR (50) ,
	pProcedureCode NVARCHAR (25) ,
	pProcedureTitle NVARCHAR (255) ,
	pStepTitle VARCHAR (255) ,
	pActionCode NVARCHAR (10) ,
	pCallNumber VARCHAR (25) ,
	pSendNumber VARCHAR (25) ,
	pId INT 
)
BEGIN
UPDATE `Log`
	SET
		`UserName` = pUserName,
		`Email` = pEmail,
		`Role` = pRole,
		`ProcedureCode` = pProcedureCode,
		`ProcedureTitle` = pProcedureTitle,
		`StepTitle` = pStepTitle,
		`ActionCode` = pActionCode,
		`CallNumber` = pCallNumber,
		`SendNumber` = pSendNumber
	WHERE `Log`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Log 
-- 
DROP PROCEDURE IF EXISTS LogDelete;
DELIMITER //
CREATE PROCEDURE `LogDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Log`
	WHERE `Log`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Log 
-- 
DROP PROCEDURE IF EXISTS LogSelectOne;
DELIMITER //
CREATE PROCEDURE `LogSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Email`,
	`Log`.`Role`,
	`Log`.`ProcedureCode`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`CallNumber`,
	`Log`.`SendNumber`,
	`Log`.`Id`,
	`Log`.`InsertedOn`
 
FROM `Log`
	WHERE `Log`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Log 
-- 
DROP PROCEDURE IF EXISTS LogSelectAll;
DELIMITER //
CREATE PROCEDURE `LogSelectAll`
(
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`
	FROM `Log`
	ORDER BY `FirstName`,`LastName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByUserName Stored Procedure for table Log
-- 
DROP PROCEDURE IF EXISTS LogSelectByUserName;
DELIMITER //
CREATE PROCEDURE `LogSelectByUserName`
(
	 pUserName NVARCHAR (50) 
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`

	FROM `Log`
	WHERE `Log`.`UserName` = pUserName
	ORDER BY `Log`.`UserName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByEmail Stored Procedure for table Log
-- 
DROP PROCEDURE IF EXISTS LogSelectByEmail;
DELIMITER //
CREATE PROCEDURE `LogSelectByEmail`
(
	 pEmail NVARCHAR (255) 
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`

	FROM `Log`
	WHERE `Log`.`Email` = pEmail
	ORDER BY `Log`.`Email`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Log
-- 
DROP PROCEDURE IF EXISTS LogSelectById;
DELIMITER //
CREATE PROCEDURE `LogSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`

	FROM `Log`
	WHERE `Log`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeUserName Stored Procedure for table Log 
-- 
DROP PROCEDURE IF EXISTS LogSelectLikeUserName;
DELIMITER //
CREATE PROCEDURE `LogSelectLikeUserName`
(
	pUserName NVARCHAR (50) 
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`
 
	FROM `Log`
	WHERE `Log`.`UserName` like CONCAT(pUserName, '%')
	ORDER BY `Log`.`UserName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeEmail Stored Procedure for table Log 
-- 
DROP PROCEDURE IF EXISTS LogSelectLikeEmail;
DELIMITER //
CREATE PROCEDURE `LogSelectLikeEmail`
(
	pEmail NVARCHAR (255) 
)
BEGIN
SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`
 
	FROM `Log`
	WHERE `Log`.`Email` like CONCAT(pEmail, '%')
	ORDER BY `Log`.`Email`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXUserName Stored Procedure for table Log
-- 
DROP PROCEDURE IF EXISTS LogSelectLikeXUserName;
DELIMITER //
CREATE PROCEDURE `LogSelectLikeXUserName`
(
	pUserName NVARCHAR (50) 
)
BEGIN
	SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`

	FROM `Log`
	WHERE `Log`.`UserName` like CONCAT('%', pUserName, '%')

	ORDER BY `Log`.`UserName` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXEmail Stored Procedure for table Log
-- 
DROP PROCEDURE IF EXISTS LogSelectLikeXEmail;
DELIMITER //
CREATE PROCEDURE `LogSelectLikeXEmail`
(
	pEmail NVARCHAR (255) 
)
BEGIN
	SELECT `Log`.`UserName`,
	`Log`.`Role`,
	`Log`.`ProcedureTitle`,
	`Log`.`StepTitle`,
	`Log`.`ActionCode`,
	`Log`.`Id`

	FROM `Log`
	WHERE `Log`.`Email` like CONCAT('%', pEmail, '%')

	ORDER BY `Log`.`Email` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for User 
-- 
DROP PROCEDURE IF EXISTS UserInsert;
DELIMITER //
CREATE PROCEDURE `UserInsert`
(
	IN pName NVARCHAR (50) ,
	IN pSalt NVARCHAR (255) ,
	IN pPassword NVARCHAR (255) ,
	IN pPersonId INT ,
	IN pRoleId INT ,
	OUT pId INT 
)
BEGIN
INSERT INTO `User`
	(
		`User`.`Name`,
		`User`.`Salt`,
		`User`.`Password`,
		`User`.`PersonId`,
		`User`.`RoleId`,
		`User`.`InsertedOn`
	)
	VALUES
	(
		pName,
		pSalt,
		pPassword,
		pPersonId,
		pRoleId,
		NOW()
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for User
-- 
DROP PROCEDURE IF EXISTS UserUpdate;
DELIMITER //
CREATE PROCEDURE `UserUpdate`
(
	pName NVARCHAR (50) ,
	pSalt NVARCHAR (255) ,
	pPassword NVARCHAR (255) ,
	pPersonId INT ,
	pRoleId INT ,
	pId INT 
)
BEGIN
UPDATE `User`
	SET
		`Name` = pName,
		`Salt` = pSalt,
		`Password` = pPassword,
		`PersonId` = pPersonId,
		`RoleId` = pRoleId
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for User 
-- 
DROP PROCEDURE IF EXISTS UserDelete;
DELIMITER //
CREATE PROCEDURE `UserDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `User`
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for User 
-- 
DROP PROCEDURE IF EXISTS UserSelectOne;
DELIMITER //
CREATE PROCEDURE `UserSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `User`.`Name`,
	`User`.`Salt`,
	`User`.`Password`,
	`JoinPersonOnPersonId`.`FirstName LastName` as `PersonFirstName LastName`,
	`User`.`PersonId`,
	`JoinRoleOnRoleId`.`Name` as `RoleName`,
	`User`.`RoleId`,
	`User`.`Id`,
	`User`.`InsertedOn`
 
FROM `User`
	INNER JOIN `Person` as `JoinPersonOnPersonId`
		ON `User`.`PersonId` = `JoinPersonOnPersonId`.`Id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `User`.`RoleId` = `JoinRoleOnRoleId`.`Id`
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table User 
-- 
DROP PROCEDURE IF EXISTS UserSelectAll;
DELIMITER //
CREATE PROCEDURE `UserSelectAll`
(
)
BEGIN
SELECT `User`.`Name`,
	`User`.`Id`
	FROM `User`
	INNER JOIN `Person` as `JoinPersonOnPersonId`
		ON `User`.`PersonId` = `JoinPersonOnPersonId`.`Id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `User`.`RoleId` = `JoinRoleOnRoleId`.`Id`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByName Stored Procedure for table User
-- 
DROP PROCEDURE IF EXISTS UserSelectByName;
DELIMITER //
CREATE PROCEDURE `UserSelectByName`
(
	 pName NVARCHAR (50) 
)
BEGIN
SELECT `User`.`Name`,
	`User`.`Id`

	FROM `User`
	INNER JOIN `Person` as `JoinPersonOnPersonId`
		ON `User`.`PersonId` = `JoinPersonOnPersonId`.`Id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `User`.`RoleId` = `JoinRoleOnRoleId`.`Id`
	WHERE `User`.`Name` = pName
	ORDER BY `User`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByPersonId Stored Procedure for table User
-- 
DROP PROCEDURE IF EXISTS UserSelectByPersonId;
DELIMITER //
CREATE PROCEDURE `UserSelectByPersonId`
(
	 pPersonId INT 
)
BEGIN
SELECT `User`.`Name`,
	`User`.`Id`

	FROM `User`
	INNER JOIN `Person` as `JoinPersonOnPersonId`
		ON `User`.`PersonId` = `JoinPersonOnPersonId`.`Id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `User`.`RoleId` = `JoinRoleOnRoleId`.`Id`
	WHERE `User`.`PersonId` = pPersonId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByRoleId Stored Procedure for table User
-- 
DROP PROCEDURE IF EXISTS UserSelectByRoleId;
DELIMITER //
CREATE PROCEDURE `UserSelectByRoleId`
(
	 pRoleId INT 
)
BEGIN
SELECT `User`.`Name`,
	`User`.`Id`

	FROM `User`
	INNER JOIN `Person` as `JoinPersonOnPersonId`
		ON `User`.`PersonId` = `JoinPersonOnPersonId`.`Id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `User`.`RoleId` = `JoinRoleOnRoleId`.`Id`
	WHERE `User`.`RoleId` = pRoleId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table User
-- 
DROP PROCEDURE IF EXISTS UserSelectById;
DELIMITER //
CREATE PROCEDURE `UserSelectById`
(
	 pId INT 
)
BEGIN
SELECT `User`.`Name`,
	`User`.`Id`

	FROM `User`
	INNER JOIN `Person` as `JoinPersonOnPersonId`
		ON `User`.`PersonId` = `JoinPersonOnPersonId`.`Id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `User`.`RoleId` = `JoinRoleOnRoleId`.`Id`
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Person 
-- 
DROP PROCEDURE IF EXISTS PersonInsert;
DELIMITER //
CREATE PROCEDURE `PersonInsert`
(
	IN pFirstName NVARCHAR (50) ,
	IN pLastName NVARCHAR (100) ,
	IN pPhoneWork VARCHAR (25) ,
	IN pPhoneHome VARCHAR (25) ,
	IN pMobile VARCHAR (25) ,
	IN pEmail NVARCHAR (255) ,
	IN pStreet NVARCHAR (255) ,
	IN pPostalCode VARCHAR (20) ,
	IN pCity NVARCHAR (80) ,
	IN pFunction NVARCHAR (50) ,
	IN pLoggedIn BIT ,
	IN pOrganisationId INT ,
	IN pRoleId INT ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Person`
	(
		`Person`.`FirstName`,
		`Person`.`LastName`,
		`Person`.`PhoneWork`,
		`Person`.`PhoneHome`,
		`Person`.`Mobile`,
		`Person`.`Email`,
		`Person`.`Street`,
		`Person`.`PostalCode`,
		`Person`.`City`,
		`Person`.`Function`,
		`Person`.`LoggedIn`,
		`Person`.`OrganisationId`,
		`Person`.`RoleId`
	)
	VALUES
	(
		pFirstName,
		pLastName,
		pPhoneWork,
		pPhoneHome,
		pMobile,
		pEmail,
		pStreet,
		pPostalCode,
		pCity,
		pFunction,
		pLoggedIn,
		pOrganisationId,
		pRoleId
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Person
-- 
DROP PROCEDURE IF EXISTS PersonUpdate;
DELIMITER //
CREATE PROCEDURE `PersonUpdate`
(
	pFirstName NVARCHAR (50) ,
	pLastName NVARCHAR (100) ,
	pPhoneWork VARCHAR (25) ,
	pPhoneHome VARCHAR (25) ,
	pMobile VARCHAR (25) ,
	pEmail NVARCHAR (255) ,
	pStreet NVARCHAR (255) ,
	pPostalCode VARCHAR (20) ,
	pCity NVARCHAR (80) ,
	pFunction NVARCHAR (50) ,
	pLoggedIn BIT ,
	pOrganisationId INT ,
	pRoleId INT ,
	pId INT 
)
BEGIN
UPDATE `Person`
	SET
		`FirstName` = pFirstName,
		`LastName` = pLastName,
		`PhoneWork` = pPhoneWork,
		`PhoneHome` = pPhoneHome,
		`Mobile` = pMobile,
		`Email` = pEmail,
		`Street` = pStreet,
		`PostalCode` = pPostalCode,
		`City` = pCity,
		`Function` = pFunction,
		`LoggedIn` = pLoggedIn,
		`OrganisationId` = pOrganisationId,
		`RoleId` = pRoleId
	WHERE `Person`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Person 
-- 
DROP PROCEDURE IF EXISTS PersonDelete;
DELIMITER //
CREATE PROCEDURE `PersonDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Person`
	WHERE `Person`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Person 
-- 
DROP PROCEDURE IF EXISTS PersonSelectOne;
DELIMITER //
CREATE PROCEDURE `PersonSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`PhoneWork`,
	`Person`.`PhoneHome`,
	`Person`.`Mobile`,
	`Person`.`Email`,
	`Person`.`Street`,
	`Person`.`PostalCode`,
	`Person`.`City`,
	`Person`.`Function`,
	`Person`.`LoggedIn`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`
 
FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Person 
-- 
DROP PROCEDURE IF EXISTS PersonSelectAll;
DELIMITER //
CREATE PROCEDURE `PersonSelectAll`
(
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`
	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	ORDER BY `LastName`,`FirstName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByFirstName Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectByFirstName;
DELIMITER //
CREATE PROCEDURE `PersonSelectByFirstName`
(
	 pFirstName NVARCHAR (50) 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`FirstName` = pFirstName
	ORDER BY `Person`.`FirstName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByLastName Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectByLastName;
DELIMITER //
CREATE PROCEDURE `PersonSelectByLastName`
(
	 pLastName NVARCHAR (100) 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`LastName` = pLastName
	ORDER BY `Person`.`LastName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByOrganisationId Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectByOrganisationId;
DELIMITER //
CREATE PROCEDURE `PersonSelectByOrganisationId`
(
	 pOrganisationId INT 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`OrganisationId` = pOrganisationId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByRoleId Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectByRoleId;
DELIMITER //
CREATE PROCEDURE `PersonSelectByRoleId`
(
	 pRoleId INT 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`RoleId` = pRoleId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectById;
DELIMITER //
CREATE PROCEDURE `PersonSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeFirstName Stored Procedure for table Person 
-- 
DROP PROCEDURE IF EXISTS PersonSelectLikeFirstName;
DELIMITER //
CREATE PROCEDURE `PersonSelectLikeFirstName`
(
	pFirstName NVARCHAR (50) 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`
 
	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`FirstName` like CONCAT(pFirstName, '%')
	ORDER BY `Person`.`FirstName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeLastName Stored Procedure for table Person 
-- 
DROP PROCEDURE IF EXISTS PersonSelectLikeLastName;
DELIMITER //
CREATE PROCEDURE `PersonSelectLikeLastName`
(
	pLastName NVARCHAR (100) 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`
 
	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`LastName` like CONCAT(pLastName, '%')
	ORDER BY `Person`.`LastName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeFunction Stored Procedure for table Person 
-- 
DROP PROCEDURE IF EXISTS PersonSelectLikeFunction;
DELIMITER //
CREATE PROCEDURE `PersonSelectLikeFunction`
(
	pFunction NVARCHAR (50) 
)
BEGIN
SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`
 
	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`Function` like CONCAT(pFunction, '%')
	ORDER BY `Person`.`Function`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXFirstName Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectLikeXFirstName;
DELIMITER //
CREATE PROCEDURE `PersonSelectLikeXFirstName`
(
	pFirstName NVARCHAR (50) 
)
BEGIN
	SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`FirstName` like CONCAT('%', pFirstName, '%')

	ORDER BY `Person`.`FirstName` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXLastName Stored Procedure for table Person
-- 
DROP PROCEDURE IF EXISTS PersonSelectLikeXLastName;
DELIMITER //
CREATE PROCEDURE `PersonSelectLikeXLastName`
(
	pLastName NVARCHAR (100) 
)
BEGIN
	SELECT `Person`.`FirstName`,
	`Person`.`LastName`,
	`Person`.`Function`,
	`JoinOrganisationOnOrganisationId`.`name` as `Organisationname`,
	`Person`.`OrganisationId`,
	`JoinRoleOnRoleId`.`role` as `Rolerole`,
	`Person`.`RoleId`,
	`Person`.`Id`

	FROM `Person`
	INNER JOIN `Organisation` as `JoinOrganisationOnOrganisationId`
		ON `Person`.`OrganisationId` = `JoinOrganisationOnOrganisationId`.`id`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `Person`.`RoleId` = `JoinRoleOnRoleId`.`id`
	WHERE `Person`.`LastName` like CONCAT('%', pLastName, '%')

	ORDER BY `Person`.`LastName` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Role 
-- 
DROP PROCEDURE IF EXISTS RoleInsert;
DELIMITER //
CREATE PROCEDURE `RoleInsert`
(
	IN pShort VARCHAR (50) ,
	IN pCode VARCHAR (50) ,
	IN pName NVARCHAR (255) ,
	IN pUpdatedOn DATETIME ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Role`
	(
		`Role`.`Short`,
		`Role`.`Code`,
		`Role`.`Name`,
		`Role`.`UpdatedOn`
	)
	VALUES
	(
		pShort,
		pCode,
		pName,
		pUpdatedOn
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Role
-- 
DROP PROCEDURE IF EXISTS RoleUpdate;
DELIMITER //
CREATE PROCEDURE `RoleUpdate`
(
	pShort VARCHAR (50) ,
	pCode VARCHAR (50) ,
	pName NVARCHAR (255) ,
	pUpdatedOn DATETIME ,
	pId INT 
)
BEGIN
UPDATE `Role`
	SET
		`Short` = pShort,
		`Code` = pCode,
		`Name` = pName,
		`UpdatedOn` = pUpdatedOn
	WHERE `Role`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Role 
-- 
DROP PROCEDURE IF EXISTS RoleDelete;
DELIMITER //
CREATE PROCEDURE `RoleDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Role`
	WHERE `Role`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Role 
-- 
DROP PROCEDURE IF EXISTS RoleSelectOne;
DELIMITER //
CREATE PROCEDURE `RoleSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Code`,
	`Role`.`Name`,
	`Role`.`UpdatedOn`,
	`Role`.`Id`
 
FROM `Role`
	WHERE `Role`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Role 
-- 
DROP PROCEDURE IF EXISTS RoleSelectAll;
DELIMITER //
CREATE PROCEDURE `RoleSelectAll`
(
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`
	FROM `Role`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByName Stored Procedure for table Role
-- 
DROP PROCEDURE IF EXISTS RoleSelectByName;
DELIMITER //
CREATE PROCEDURE `RoleSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`

	FROM `Role`
	WHERE `Role`.`Name` = pName
	ORDER BY `Role`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Role
-- 
DROP PROCEDURE IF EXISTS RoleSelectById;
DELIMITER //
CREATE PROCEDURE `RoleSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`

	FROM `Role`
	WHERE `Role`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeShort Stored Procedure for table Role 
-- 
DROP PROCEDURE IF EXISTS RoleSelectLikeShort;
DELIMITER //
CREATE PROCEDURE `RoleSelectLikeShort`
(
	pShort VARCHAR (50) 
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`
 
	FROM `Role`
	WHERE `Role`.`Short` like CONCAT(pShort, '%')
	ORDER BY `Role`.`Short`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeCode Stored Procedure for table Role 
-- 
DROP PROCEDURE IF EXISTS RoleSelectLikeCode;
DELIMITER //
CREATE PROCEDURE `RoleSelectLikeCode`
(
	pCode VARCHAR (50) 
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`
 
	FROM `Role`
	WHERE `Role`.`Code` like CONCAT(pCode, '%')
	ORDER BY `Role`.`Code`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeName Stored Procedure for table Role 
-- 
DROP PROCEDURE IF EXISTS RoleSelectLikeName;
DELIMITER //
CREATE PROCEDURE `RoleSelectLikeName`
(
	pName NVARCHAR (255) 
)
BEGIN
SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`
 
	FROM `Role`
	WHERE `Role`.`Name` like CONCAT(pName, '%')
	ORDER BY `Role`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXName Stored Procedure for table Role
-- 
DROP PROCEDURE IF EXISTS RoleSelectLikeXName;
DELIMITER //
CREATE PROCEDURE `RoleSelectLikeXName`
(
	pName NVARCHAR (255) 
)
BEGIN
	SELECT `Role`.`Short`,
	`Role`.`Name`,
	`Role`.`Id`

	FROM `Role`
	WHERE `Role`.`Name` like CONCAT('%', pName, '%')

	ORDER BY `Role`.`Name` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureInsert;
DELIMITER //
CREATE PROCEDURE `ProcedureInsert`
(
	IN pCode VARCHAR (50) ,
	IN pNaam NVARCHAR (255) ,
	IN pHeading NVARCHAR (255) ,
	IN pDescription NVARCHAR (255) ,
	IN pUpdatedOn DATETIME ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Procedure`
	(
		`Procedure`.`Code`,
		`Procedure`.`Naam`,
		`Procedure`.`Heading`,
		`Procedure`.`Description`,
		`Procedure`.`UpdatedOn`
	)
	VALUES
	(
		pCode,
		pNaam,
		pHeading,
		pDescription,
		pUpdatedOn
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Procedure
-- 
DROP PROCEDURE IF EXISTS ProcedureUpdate;
DELIMITER //
CREATE PROCEDURE `ProcedureUpdate`
(
	pCode VARCHAR (50) ,
	pNaam NVARCHAR (255) ,
	pHeading NVARCHAR (255) ,
	pDescription NVARCHAR (255) ,
	pUpdatedOn DATETIME ,
	pId INT 
)
BEGIN
UPDATE `Procedure`
	SET
		`Code` = pCode,
		`Naam` = pNaam,
		`Heading` = pHeading,
		`Description` = pDescription,
		`UpdatedOn` = pUpdatedOn
	WHERE `Procedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureDelete;
DELIMITER //
CREATE PROCEDURE `ProcedureDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Procedure`
	WHERE `Procedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectOne;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Heading`,
	`Procedure`.`Description`,
	`Procedure`.`UpdatedOn`,
	`Procedure`.`Id`
 
FROM `Procedure`
	WHERE `Procedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectAll;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectAll`
(
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`
	FROM `Procedure`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByNaam Stored Procedure for table Procedure
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectByNaam;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectByNaam`
(
	 pNaam NVARCHAR (255) 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`

	FROM `Procedure`
	WHERE `Procedure`.`Naam` = pNaam
	ORDER BY `Procedure`.`Naam`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByHeading Stored Procedure for table Procedure
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectByHeading;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectByHeading`
(
	 pHeading NVARCHAR (255) 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`

	FROM `Procedure`
	WHERE `Procedure`.`Heading` = pHeading
	ORDER BY `Procedure`.`Heading`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Procedure
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectById;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`

	FROM `Procedure`
	WHERE `Procedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeCode Stored Procedure for table Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectLikeCode;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectLikeCode`
(
	pCode VARCHAR (50) 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`
 
	FROM `Procedure`
	WHERE `Procedure`.`Code` like CONCAT(pCode, '%')
	ORDER BY `Procedure`.`Code`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeNaam Stored Procedure for table Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectLikeNaam;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectLikeNaam`
(
	pNaam NVARCHAR (255) 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`
 
	FROM `Procedure`
	WHERE `Procedure`.`Naam` like CONCAT(pNaam, '%')
	ORDER BY `Procedure`.`Naam`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeHeading Stored Procedure for table Procedure 
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectLikeHeading;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectLikeHeading`
(
	pHeading NVARCHAR (255) 
)
BEGIN
SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`
 
	FROM `Procedure`
	WHERE `Procedure`.`Heading` like CONCAT(pHeading, '%')
	ORDER BY `Procedure`.`Heading`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXNaam Stored Procedure for table Procedure
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectLikeXNaam;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectLikeXNaam`
(
	pNaam NVARCHAR (255) 
)
BEGIN
	SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`

	FROM `Procedure`
	WHERE `Procedure`.`Naam` like CONCAT('%', pNaam, '%')

	ORDER BY `Procedure`.`Naam` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXHeading Stored Procedure for table Procedure
-- 
DROP PROCEDURE IF EXISTS ProcedureSelectLikeXHeading;
DELIMITER //
CREATE PROCEDURE `ProcedureSelectLikeXHeading`
(
	pHeading NVARCHAR (255) 
)
BEGIN
	SELECT `Procedure`.`Code`,
	`Procedure`.`Naam`,
	`Procedure`.`Id`

	FROM `Procedure`
	WHERE `Procedure`.`Heading` like CONCAT('%', pHeading, '%')

	ORDER BY `Procedure`.`Heading` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for RoleProcedure 
-- 
DROP PROCEDURE IF EXISTS RoleProcedureInsert;
DELIMITER //
CREATE PROCEDURE `RoleProcedureInsert`
(
	IN pCode VARCHAR (50) ,
	IN pRoleId INT ,
	IN pProcedureId INT ,
	OUT pId INT 
)
BEGIN
INSERT INTO `RoleProcedure`
	(
		`RoleProcedure`.`Code`,
		`RoleProcedure`.`RoleId`,
		`RoleProcedure`.`ProcedureId`
	)
	VALUES
	(
		pCode,
		pRoleId,
		pProcedureId
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for RoleProcedure
-- 
DROP PROCEDURE IF EXISTS RoleProcedureUpdate;
DELIMITER //
CREATE PROCEDURE `RoleProcedureUpdate`
(
	pCode VARCHAR (50) ,
	pRoleId INT ,
	pProcedureId INT ,
	pId INT 
)
BEGIN
UPDATE `RoleProcedure`
	SET
		`Code` = pCode,
		`RoleId` = pRoleId,
		`ProcedureId` = pProcedureId
	WHERE `RoleProcedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for RoleProcedure 
-- 
DROP PROCEDURE IF EXISTS RoleProcedureDelete;
DELIMITER //
CREATE PROCEDURE `RoleProcedureDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `RoleProcedure`
	WHERE `RoleProcedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for RoleProcedure 
-- 
DROP PROCEDURE IF EXISTS RoleProcedureSelectOne;
DELIMITER //
CREATE PROCEDURE `RoleProcedureSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `RoleProcedure`.`Code`,
	`JoinRoleOnRoleId`.`title` as `Roletitle`,
	`RoleProcedure`.`RoleId`,
	`JoinProecedureOnProcedureId`.`title` as `Proeceduretitle`,
	`RoleProcedure`.`ProcedureId`,
	`RoleProcedure`.`Id`
 
FROM `RoleProcedure`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `RoleProcedure`.`RoleId` = `JoinRoleOnRoleId`.`id`
	INNER JOIN `Proecedure` as `JoinProecedureOnProcedureId`
		ON `RoleProcedure`.`ProcedureId` = `JoinProecedureOnProcedureId`.`id`
	WHERE `RoleProcedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table RoleProcedure 
-- 
DROP PROCEDURE IF EXISTS RoleProcedureSelectAll;
DELIMITER //
CREATE PROCEDURE `RoleProcedureSelectAll`
(
)
BEGIN
SELECT `RoleProcedure`.`Code`,
	`JoinRoleOnRoleId`.`title` as `Roletitle`,
	`RoleProcedure`.`RoleId`,
	`JoinProecedureOnProcedureId`.`title` as `Proeceduretitle`,
	`RoleProcedure`.`ProcedureId`,
	`RoleProcedure`.`Id`
	FROM `RoleProcedure`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `RoleProcedure`.`RoleId` = `JoinRoleOnRoleId`.`id`
	INNER JOIN `Proecedure` as `JoinProecedureOnProcedureId`
		ON `RoleProcedure`.`ProcedureId` = `JoinProecedureOnProcedureId`.`id`
	ORDER BY `Code`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByRoleId Stored Procedure for table RoleProcedure
-- 
DROP PROCEDURE IF EXISTS RoleProcedureSelectByRoleId;
DELIMITER //
CREATE PROCEDURE `RoleProcedureSelectByRoleId`
(
	 pRoleId INT 
)
BEGIN
SELECT `RoleProcedure`.`Code`,
	`JoinRoleOnRoleId`.`title` as `Roletitle`,
	`RoleProcedure`.`RoleId`,
	`JoinProecedureOnProcedureId`.`title` as `Proeceduretitle`,
	`RoleProcedure`.`ProcedureId`,
	`RoleProcedure`.`Id`

	FROM `RoleProcedure`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `RoleProcedure`.`RoleId` = `JoinRoleOnRoleId`.`id`
	INNER JOIN `Proecedure` as `JoinProecedureOnProcedureId`
		ON `RoleProcedure`.`ProcedureId` = `JoinProecedureOnProcedureId`.`id`
	WHERE `RoleProcedure`.`RoleId` = pRoleId
	ORDER BY `RoleProcedure`.`RoleId`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByProcedureId Stored Procedure for table RoleProcedure
-- 
DROP PROCEDURE IF EXISTS RoleProcedureSelectByProcedureId;
DELIMITER //
CREATE PROCEDURE `RoleProcedureSelectByProcedureId`
(
	 pProcedureId INT 
)
BEGIN
SELECT `RoleProcedure`.`Code`,
	`JoinRoleOnRoleId`.`title` as `Roletitle`,
	`RoleProcedure`.`RoleId`,
	`JoinProecedureOnProcedureId`.`title` as `Proeceduretitle`,
	`RoleProcedure`.`ProcedureId`,
	`RoleProcedure`.`Id`

	FROM `RoleProcedure`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `RoleProcedure`.`RoleId` = `JoinRoleOnRoleId`.`id`
	INNER JOIN `Proecedure` as `JoinProecedureOnProcedureId`
		ON `RoleProcedure`.`ProcedureId` = `JoinProecedureOnProcedureId`.`id`
	WHERE `RoleProcedure`.`ProcedureId` = pProcedureId
	ORDER BY `RoleProcedure`.`ProcedureId`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table RoleProcedure
-- 
DROP PROCEDURE IF EXISTS RoleProcedureSelectById;
DELIMITER //
CREATE PROCEDURE `RoleProcedureSelectById`
(
	 pId INT 
)
BEGIN
SELECT `RoleProcedure`.`Code`,
	`JoinRoleOnRoleId`.`title` as `Roletitle`,
	`RoleProcedure`.`RoleId`,
	`JoinProecedureOnProcedureId`.`title` as `Proeceduretitle`,
	`RoleProcedure`.`ProcedureId`,
	`RoleProcedure`.`Id`

	FROM `RoleProcedure`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `RoleProcedure`.`RoleId` = `JoinRoleOnRoleId`.`id`
	INNER JOIN `Proecedure` as `JoinProecedureOnProcedureId`
		ON `RoleProcedure`.`ProcedureId` = `JoinProecedureOnProcedureId`.`id`
	WHERE `RoleProcedure`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeCode Stored Procedure for table RoleProcedure 
-- 
DROP PROCEDURE IF EXISTS RoleProcedureSelectLikeCode;
DELIMITER //
CREATE PROCEDURE `RoleProcedureSelectLikeCode`
(
	pCode VARCHAR (50) 
)
BEGIN
SELECT `RoleProcedure`.`Code`,
	`JoinRoleOnRoleId`.`title` as `Roletitle`,
	`RoleProcedure`.`RoleId`,
	`JoinProecedureOnProcedureId`.`title` as `Proeceduretitle`,
	`RoleProcedure`.`ProcedureId`,
	`RoleProcedure`.`Id`
 
	FROM `RoleProcedure`
	INNER JOIN `Role` as `JoinRoleOnRoleId`
		ON `RoleProcedure`.`RoleId` = `JoinRoleOnRoleId`.`id`
	INNER JOIN `Proecedure` as `JoinProecedureOnProcedureId`
		ON `RoleProcedure`.`ProcedureId` = `JoinProecedureOnProcedureId`.`id`
	WHERE `RoleProcedure`.`Code` like CONCAT(pCode, '%')
	ORDER BY `RoleProcedure`.`Code`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Step 
-- 
DROP PROCEDURE IF EXISTS StepInsert;
DELIMITER //
CREATE PROCEDURE `StepInsert`
(
	IN pName NVARCHAR (255) ,
	IN pActionId INT ,
	IN pDescription NVARCHAR (255) ,
	IN pRoleProcedureId INT ,
	IN pOrder INT ,
	IN pData NVARCHAR (4096) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Step`
	(
		`Step`.`Name`,
		`Step`.`ActionId`,
		`Step`.`Description`,
		`Step`.`RoleProcedureId`,
		`Step`.`Order`,
		`Step`.`Data`
	)
	VALUES
	(
		pName,
		pActionId,
		pDescription,
		pRoleProcedureId,
		pOrder,
		pData
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Step
-- 
DROP PROCEDURE IF EXISTS StepUpdate;
DELIMITER //
CREATE PROCEDURE `StepUpdate`
(
	pName NVARCHAR (255) ,
	pActionId INT ,
	pDescription NVARCHAR (255) ,
	pRoleProcedureId INT ,
	pOrder INT ,
	pData NVARCHAR (4096) ,
	pId INT 
)
BEGIN
UPDATE `Step`
	SET
		`Name` = pName,
		`ActionId` = pActionId,
		`Description` = pDescription,
		`RoleProcedureId` = pRoleProcedureId,
		`Order` = pOrder,
		`Data` = pData
	WHERE `Step`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Step 
-- 
DROP PROCEDURE IF EXISTS StepDelete;
DELIMITER //
CREATE PROCEDURE `StepDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Step`
	WHERE `Step`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Step 
-- 
DROP PROCEDURE IF EXISTS StepSelectOne;
DELIMITER //
CREATE PROCEDURE `StepSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`Step`.`Description`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Data`,
	`Step`.`Id`
 
FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Step 
-- 
DROP PROCEDURE IF EXISTS StepSelectAll;
DELIMITER //
CREATE PROCEDURE `StepSelectAll`
(
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`
	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByName Stored Procedure for table Step
-- 
DROP PROCEDURE IF EXISTS StepSelectByName;
DELIMITER //
CREATE PROCEDURE `StepSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`

	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`Name` = pName
	ORDER BY `Step`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByActionId Stored Procedure for table Step
-- 
DROP PROCEDURE IF EXISTS StepSelectByActionId;
DELIMITER //
CREATE PROCEDURE `StepSelectByActionId`
(
	 pActionId INT 
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`

	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`ActionId` = pActionId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByRoleProcedureId Stored Procedure for table Step
-- 
DROP PROCEDURE IF EXISTS StepSelectByRoleProcedureId;
DELIMITER //
CREATE PROCEDURE `StepSelectByRoleProcedureId`
(
	 pRoleProcedureId INT 
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`

	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`RoleProcedureId` = pRoleProcedureId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Step
-- 
DROP PROCEDURE IF EXISTS StepSelectById;
DELIMITER //
CREATE PROCEDURE `StepSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`

	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeName Stored Procedure for table Step 
-- 
DROP PROCEDURE IF EXISTS StepSelectLikeName;
DELIMITER //
CREATE PROCEDURE `StepSelectLikeName`
(
	pName NVARCHAR (255) 
)
BEGIN
SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`
 
	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`Name` like CONCAT(pName, '%')
	ORDER BY `Step`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXName Stored Procedure for table Step
-- 
DROP PROCEDURE IF EXISTS StepSelectLikeXName;
DELIMITER //
CREATE PROCEDURE `StepSelectLikeXName`
(
	pName NVARCHAR (255) 
)
BEGIN
	SELECT `Step`.`Name`,
	`JoinActionOnActionId`.`Code` as `ActionCode`,
	`Step`.`ActionId`,
	`JoinRoleProcedureOnRoleProcedureId`.`Code` as `RoleProcedureCode`,
	`Step`.`RoleProcedureId`,
	`Step`.`Order`,
	`Step`.`Id`

	FROM `Step`
	INNER JOIN `Action` as `JoinActionOnActionId`
		ON `Step`.`ActionId` = `JoinActionOnActionId`.`Id`
	INNER JOIN `RoleProcedure` as `JoinRoleProcedureOnRoleProcedureId`
		ON `Step`.`RoleProcedureId` = `JoinRoleProcedureOnRoleProcedureId`.`Id`
	WHERE `Step`.`Name` like CONCAT('%', pName, '%')

	ORDER BY `Step`.`Name` ;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Insert Stored Procedure for Action 
-- 
DROP PROCEDURE IF EXISTS ActionInsert;
DELIMITER //
CREATE PROCEDURE `ActionInsert`
(
	IN pCode NVARCHAR (10) ,
	IN pName NVARCHAR (255) ,
	IN pData NVARCHAR (255) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Action`
	(
		`Action`.`Code`,
		`Action`.`Name`,
		`Action`.`Data`
	)
	VALUES
	(
		pCode,
		pName,
		pData
	);
	-- return the Id of the inserted row
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Update Stored Procedure for Action
-- 
DROP PROCEDURE IF EXISTS ActionUpdate;
DELIMITER //
CREATE PROCEDURE `ActionUpdate`
(
	pCode NVARCHAR (10) ,
	pName NVARCHAR (255) ,
	pData NVARCHAR (255) ,
	pId INT 
)
BEGIN
UPDATE `Action`
	SET
		`Code` = pCode,
		`Name` = pName,
		`Data` = pData
	WHERE `Action`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML Delete Stored Procedure for Action 
-- 
DROP PROCEDURE IF EXISTS ActionDelete;
DELIMITER //
CREATE PROCEDURE `ActionDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Action`
	WHERE `Action`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectOne Stored Procedure for Action 
-- 
DROP PROCEDURE IF EXISTS ActionSelectOne;
DELIMITER //
CREATE PROCEDURE `ActionSelectOne`
(
	 pId INT 
)
BEGIN
SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Data`,
	`Action`.`Id`
 
FROM `Action`
	WHERE `Action`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectAll Stored Procedure for table Action 
-- 
DROP PROCEDURE IF EXISTS ActionSelectAll;
DELIMITER //
CREATE PROCEDURE `ActionSelectAll`
(
)
BEGIN
SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Id`
	FROM `Action`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByCode Stored Procedure for table Action
-- 
DROP PROCEDURE IF EXISTS ActionSelectByCode;
DELIMITER //
CREATE PROCEDURE `ActionSelectByCode`
(
	 pCode NVARCHAR (10) 
)
BEGIN
SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Id`

	FROM `Action`
	WHERE `Action`.`Code` = pCode
	ORDER BY `Action`.`Code`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectByName Stored Procedure for table Action
-- 
DROP PROCEDURE IF EXISTS ActionSelectByName;
DELIMITER //
CREATE PROCEDURE `ActionSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Id`

	FROM `Action`
	WHERE `Action`.`Name` = pName
	ORDER BY `Action`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectById Stored Procedure for table Action
-- 
DROP PROCEDURE IF EXISTS ActionSelectById;
DELIMITER //
CREATE PROCEDURE `ActionSelectById`
(
	 pId INT 
)
BEGIN
SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Id`

	FROM `Action`
	WHERE `Action`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeName Stored Procedure for table Action 
-- 
DROP PROCEDURE IF EXISTS ActionSelectLikeName;
DELIMITER //
CREATE PROCEDURE `ActionSelectLikeName`
(
	pName NVARCHAR (255) 
)
BEGIN
SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Id`
 
	FROM `Action`
	WHERE `Action`.`Name` like CONCAT(pName, '%')
	ORDER BY `Action`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Tuesday 27th of April 2021 09:57:25 AM
-- DML SelectLikeXName Stored Procedure for table Action
-- 
DROP PROCEDURE IF EXISTS ActionSelectLikeXName;
DELIMITER //
CREATE PROCEDURE `ActionSelectLikeXName`
(
	pName NVARCHAR (255) 
)
BEGIN
	SELECT `Action`.`Code`,
	`Action`.`Name`,
	`Action`.`Id`

	FROM `Action`
	WHERE `Action`.`Name` like CONCAT('%', pName, '%')

	ORDER BY `Action`.`Name` ;
END //
DELIMITER ;