Mikmak MsSQL DML Product

CRUD stored procedures voor de Product tabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een product kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van een gedeelte van de naam, en van de leverancier. De Product tabel heeft één foreign key kolom met de naam IdSupplier. De waarde in deze kolom verwijst naar een waarde in de primary key kolom Id van de tabel Supplier. In de SelectOne stored procedure moet de naam van de leverancier mee opgehaald worden. Immers de gebruiker geeft niets om id's, die gebruiken wij, programmeurs, intern om de tabellen aan elkaar te koppelen.

Design

Op basis van het Mikmak logisch model maken we de stored procedures. Naast de standaard stored procedures, Insert, Update, Delete, SelectOne, SelectAll, maken we voor elke tabelkolom waarbij de Searchable is ingesteld op SELECTBY maken we een stored procedure waarbij er gezocht kan worden op deze kolom in de tabel.

Oplossing

Insert

De procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML Insert Stored Procedure for Product 
-- 
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'ProductInsert'))
BEGIN
    DROP PROCEDURE ProductInsert
END
GO
CREATE PROCEDURE "ProductInsert"
(
    @Description NVARCHAR (1024) ,
    @Name NVARCHAR (255) ,
    @Price FLOAT ,
    @ShippingCost FLOAT ,
    @TotalRating INT ,
    @Thumbnail VARCHAR (255) ,
    @Image VARCHAR (255) ,
    @DiscountPercentage FLOAT ,
    @Votes INT ,
    @Id INT  output,
    @IdSupplier INT 
)
AS
BEGIN
INSERT INTO "Product"
    (
        "Product"."Description",
        "Product"."Name",
        "Product"."Price",
        "Product"."ShippingCost",
        "Product"."TotalRating",
        "Product"."Thumbnail",
        "Product"."Image",
        "Product"."DiscountPercentage",
        "Product"."Votes",
        "Product"."IdSupplier"
    )
    VALUES
    (
        @Description,
        @Name,
        @Price,
        @ShippingCost,
        @TotalRating,
        @Thumbnail,
        @Image,
        @DiscountPercentage,
        @Votes,
        @IdSupplier
    );
    set @Id = SCOPE_IDENTITY();
END 
GO

Test

use Mikmak
go
-- zoek eerst de id van de supplier op
declare @IdSupplier int
set @IdSupplier = (select Id from Supplier where Name = 'De Schoenmaker')
-- insert
declare @NewId int
exec ProductInsert 'Met deze schoenen loop je tot naar Timbuktu', 
        'Nice Boots', 20.5, 3.5, 5, 
        'http://www.ida.classy.be/wp18/Schoen_strikken.jpg', 
        'http://www.schilberg.nl/sinterklaas/2006/schoen1.jpg', 
        0.10, 245, @NewId OUTPUT, @IdSupplier 
print @NewId

Update

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML Update Stored Procedure for Product
-- 
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'ProductUpdate'))
BEGIN
    DROP PROCEDURE ProductUpdate
END
GO
CREATE PROCEDURE "ProductUpdate"
(
    @Description NVARCHAR (1024) ,
    @Name NVARCHAR (255) ,
    @Price FLOAT ,
    @ShippingCost FLOAT ,
    @TotalRating INT ,
    @Thumbnail VARCHAR (255) ,
    @Image VARCHAR (255) ,
    @DiscountPercentage FLOAT ,
    @Votes INT ,
    @Id INT ,
    @IdSupplier INT 
)
AS
BEGIN
UPDATE "Product"
    SET
        "Description" = @Description,
        "Name" = @Name,
        "Price" = @Price,
        "ShippingCost" = @ShippingCost,
        "TotalRating" = @TotalRating,
        "Thumbnail" = @Thumbnail,
        "Image" = @Image,
        "DiscountPercentage" = @DiscountPercentage,
        "Votes" = @Votes,
        "IdSupplier" = @IdSupplier
    WHERE "Product"."Id" = @Id;
END 
GO

Test

-- zoek eerst de id van de supplier op
declare @IdSupplier int
set @IdSupplier = (select Id from Supplier where Name = 'De Schoenmaker')
-- zoek dan de id van de product op
declare @IdProduct int
set @IdProduct = (select Id from Product where Name = 'Nice Boots')
-- update de beschrijving van het product
exec ProductUpdate 'Met deze schoenen loop je tot naar Timbuktu en nog verder!', 
        'Nice Boots', 20.5, 3.5, 5, 
        'http://www.ida.classy.be/wp18/Schoen_strikken.jpg', 
        'http://www.schilberg.nl/sinterklaas/2006/schoen1.jpg', 
        0.10, 245, @IdProduct, @IdSupplier 

Delete

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML Delete Stored Procedure for Product 
-- 
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'ProductDelete'))
BEGIN
    DROP PROCEDURE ProductDelete
END
GO
CREATE PROCEDURE "ProductDelete"
(
     @Id INT 
)
AS
BEGIN
DELETE FROM "Product"
    WHERE "Product"."Id" = @Id;
END 
GO

Test

use Mikmak
go
-- zoek de id van de product op
declare @IdProduct int
set @IdProduct = (select Id from Product where Name = 'Nice Boots')
-- delete het product
exec ProductDelete @IdProduct

Voeg het gedelete product weer toe. Let erop dat de Id nu 2 is. De Id's van gedelete rijen worden niet hergebruikt!

use Mikmak
go
-- zoek eerst de id van de supplier op
declare @IdSupplier int
set @IdSupplier = (select Id from Supplier where Name = 'De Schoenmaker')
-- insert
declare @NewId int
exec ProductInsert 'Met deze schoenen loop je tot naar Timbuktu', 
        'Nice Boots', 20.5, 3.5, 5, 
        'http://www.ida.classy.be/wp18/Schoen_strikken.jpg', 
        'http://www.schilberg.nl/sinterklaas/2006/schoen1.jpg', 
        0.10, 245, @NewId OUTPUT, @IdSupplier 
print @NewId

SelectOne

We hebben niet alleen de Id van de leverancier nodig maar ook de Name.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML SelectOne Stored Procedure for Product 
-- 
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'ProductSelectOne'))
BEGIN
    DROP PROCEDURE ProductSelectOne
END
GO
CREATE PROCEDURE "ProductSelectOne"
(
     @Id INT 
)
AS
BEGIN
SELECT "Description",
    "Product"."Name",
    "Price",
    "ShippingCost",
    "TotalRating",
    "Thumbnail",
    "Image",
    "DiscountPercentage",
    "Votes",
    "Product"."Id",
    "IdSupplier",
    "Supplier"."Name" AS "SupplierName"
    FROM "Product"
    INNER JOIN Supplier 
        ON "Product"."IdSupplier" = "Supplier"."Id"
    WHERE "Product"."Id" = @Id;
END 
GO

Test

use Mikmak
go
-- zoek de id van de product op
declare @IdProduct int
set @IdProduct = (select Id from Product where Name = 'Nice Boots')
-- delete het product
exec ProductSelectOne @IdProduct

SelectAll

De stored procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML SelectAll Stored Procedure for table Product 
-- 
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'ProductSelectAll'))
BEGIN
    DROP PROCEDURE ProductSelectAll
END
GO
CREATE PROCEDURE "ProductSelectAll"
AS
BEGIN
SELECT "Product"."Name",
    "Product"."Price",
    "Product"."ShippingCost",
    "Product"."TotalRating",
    "Product"."DiscountPercentage",
    "Product"."Votes",
    "Product"."Id",
    "Supplier"."Name" as "SupplierName",
    "Product"."IdSupplier"
    FROM "Product"
    INNER JOIN "Supplier"
        ON "Product"."IdSupplier" = "Supplier"."Id"
    ORDER BY "Name";
END 
GO

Test

use Mikmak
go
exec ProductSelectAll

SelectXName

De X in de naam staat voor een willekeurige waarde in de naam. In de WHERE clausule van het SQL statement gebruiken we daarvoor de LIKE operator en twee % jokers, een voor de tekst die we zoeken en één erna.

De procedure maken

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML SelectLikeXName Stored Procedure for table Product
-- 
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'ProductSelectLikeXName'))
BEGIN
    DROP PROCEDURE ProductSelectLikeXName
END
GO
CREATE PROCEDURE "ProductSelectLikeXName"
(
    @Name NVARCHAR (255) 
)
AS
BEGIN
    SELECT "Product"."Name",
    "Product"."Price",
    "Product"."ShippingCost",
    "Product"."TotalRating",
    "Product"."DiscountPercentage",
    "Product"."Votes",
    "Product"."Id",
    "Supplier"."Name" as "SupplierName",
    "Product"."IdSupplier"

    FROM "Product"
    INNER JOIN "Supplier"
        ON "Product"."IdSupplier" = "Supplier"."Id"
    WHERE "Product"."Name" like CONCAT('%', @Name, '%')

    ORDER BY "Product"."Name" ;
END 
GO

Test

Alle drie de statements gaan het product met de naam 'Nice Boots' retourneren:

exec ProductSelectLikeXName 'boot'
exec ProductSelectLikeXName 'nice'
exec ProductSelectLikeXName 'boot'

SelectByIdSupplier

Maak de stored procedure

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML SelectByIdSupplier Stored Procedure for table Product
-- 
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'ProductSelectByIdSupplier'))
BEGIN
    DROP PROCEDURE ProductSelectByIdSupplier
END
GO
CREATE PROCEDURE "ProductSelectByIdSupplier"
(
     @IdSupplier INT 
)
AS
BEGIN
SELECT "Product"."Name",
    "Product"."Price",
    "Product"."ShippingCost",
    "Product"."TotalRating",
    "Product"."DiscountPercentage",
    "Product"."Votes",
    "Product"."Id",
    "Supplier"."Name" as "SupplierName",
    "Product"."IdSupplier"

    FROM "Product"
    INNER JOIN "Supplier" as "Supplier"
        ON "Product"."IdSupplier" = "Supplier"."Id"
    WHERE "Product"."IdSupplier" = @IdSupplier;
END 
GO

Test

-- zoek eerst de id van de supplier op
declare @IdSupplier int
set @IdSupplier = (select Id from Supplier where Name = 'De Schoenmaker')
-- delete het product
exec ProductSelectByIdSupplier @IdSupplier

SelectByNameSupplier

Deze stored procedure is anders dan de vorige. We willen nu niet meer zoeken op de Id van Supplier in de Product tabel, maar op de naam van de leverancier. Die staat niet in de tabel Product dus moeten we die koppelen aan de tabel Supplier en op de Name kolom van deze tabel zoeken.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Saturday 9th of January 2016 01:46:21 PM
-- DML SelectByNameSupplier Stored Procedure for table Product
-- 
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'ProductSelectByNameSupplier'))
BEGIN
    DROP PROCEDURE ProductSelectByNameSupplier
END
GO
CREATE PROCEDURE "ProductSelectByNameSupplier"
(
     @NameSupplier NVARCHAR(255) 
)
AS
BEGIN
SELECT "Product"."Name",
    "Product"."Price",
    "Product"."ShippingCost",
    "Product"."TotalRating",
    "Product"."DiscountPercentage",
    "Product"."Votes",
    "Product"."Id",
    "Supplier"."Name" as "SupplierName",
    "Product"."IdSupplier"

    FROM "Product"
    INNER JOIN "Supplier" as "Supplier"
        ON "Product"."IdSupplier" = "Supplier"."Id"
    WHERE "Supplier"."Name" = @NameSupplier;
END 
GO

Test

exec ProductSelectByNameSupplier 'De Schoenmaker'