Mikmak MySQL DML UnitBase

Probleem

We moeten de gegevens van een basiseenheid kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam en van de code.

Design

Oplossing

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML Insert Stored Procedure for UnitBase 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseInsert'))
BEGIN
    DROP PROCEDURE UnitBaseInsert
END
GO
CREATE PROCEDURE "UnitBaseInsert"
(
    @Name NVARCHAR (255) ,
    @Description NVARCHAR (1024) ,
    @ShippingCostMultiplier FLOAT ,
    @Code NVARCHAR (2) ,
    @Id INT  output
)
AS
BEGIN
INSERT INTO "UnitBase"
    (
        "UnitBase"."Name",
        "UnitBase"."Description",
        "UnitBase"."ShippingCostMultiplier",
        "UnitBase"."Code"
    )
    VALUES
    (
        @Name,
        @Description,
        @ShippingCostMultiplier,
        @Code
    );
    set @Id = SCOPE_IDENTITY();
END 
GO

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML Update Stored Procedure for UnitBase
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseUpdate'))
BEGIN
    DROP PROCEDURE UnitBaseUpdate
END
GO
CREATE PROCEDURE "UnitBaseUpdate"
(
    @Name NVARCHAR (255) ,
    @Description NVARCHAR (1024) ,
    @ShippingCostMultiplier FLOAT ,
    @Code NVARCHAR (2) ,
    @Id INT 
)
AS
BEGIN
UPDATE "UnitBase"
    SET
        "Name" = @Name,
        "Description" = @Description,
        "ShippingCostMultiplier" = @ShippingCostMultiplier,
        "Code" = @Code
    WHERE "UnitBase"."Id" = @Id;
END 
GO

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML Delete Stored Procedure for UnitBase 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseDelete'))
BEGIN
    DROP PROCEDURE UnitBaseDelete
END
GO
CREATE PROCEDURE "UnitBaseDelete"
(
     @Id INT 
)
AS
BEGIN
DELETE FROM "UnitBase"
    WHERE "UnitBase"."Id" = @Id;
END 
GO

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectOne Stored Procedure for UnitBase 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseSelectOne'))
BEGIN
    DROP PROCEDURE UnitBaseSelectOne
END
GO
CREATE PROCEDURE "UnitBaseSelectOne"
(
     @Id INT 
)
AS
BEGIN
SELECT * FROM "UnitBase"
    WHERE "UnitBase"."Id" = @Id;
END 
GO

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectAll Stored Procedure for table UnitBase 
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseSelectAll'))
BEGIN
    DROP PROCEDURE UnitBaseSelectAll
END
GO
CREATE PROCEDURE "UnitBaseSelectAll"
AS
BEGIN
SELECT "UnitBase"."Name",
    "UnitBase"."ShippingCostMultiplier",
    "UnitBase"."Code",
    "UnitBase"."Id"
    FROM "UnitBase"
    ORDER BY "Name";
END 
GO

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectByName Stored Procedure for table UnitBase
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseSelectByName'))
BEGIN
    DROP PROCEDURE UnitBaseSelectByName
END
GO
CREATE PROCEDURE "UnitBaseSelectByName"
(
     @Name NVARCHAR (255) 
)
AS
BEGIN
SELECT "UnitBase"."Name",
    "UnitBase"."ShippingCostMultiplier",
    "UnitBase"."Code",
    "UnitBase"."Id"

    FROM "UnitBase"
    WHERE "UnitBase"."Name" = @Name
    ORDER BY "UnitBase"."Name";
END 
GO

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Sunday 3rd of January 2016 02:28:16 PM
-- DML SelectByCode Stored Procedure for table UnitBase
-- 
USE Mikmak;
-- Vooraleer de stored procedure te creëren, test als
-- de die al bestaat. Als de SP al bestaat
-- moet je die eerst droppen
IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = OBJECT_ID(N'UnitBaseSelectByCode'))
BEGIN
    DROP PROCEDURE UnitBaseSelectByCode
END
GO
CREATE PROCEDURE "UnitBaseSelectByCode"
(
     @Code NVARCHAR (2) 
)
AS
BEGIN
SELECT "UnitBase"."Name",
    "UnitBase"."ShippingCostMultiplier",
    "UnitBase"."Code",
    "UnitBase"."Id"

    FROM "UnitBase"
    WHERE "UnitBase"."Code" = @Code
    ORDER BY "UnitBase"."Code";
END 
GO