DML Supplier

Probleem

We moeten de gegevens van een leverancier kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam, de code en de contactpersoon. De Supplier tabel heeft één foreign key kolom met de naam IdCountry. De waarde in deze kolom verwijst naar een waarde in de primary key kolom Id van de tabel Country. In de SelectOne stored procedure moet de naam van het land 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

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 11:45:34 AM
-- DML Insert Stored Procedure for Supplier 
-- 
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'SupplierInsert'))
BEGIN
    DROP PROCEDURE SupplierInsert
END
GO
CREATE PROCEDURE "SupplierInsert"
(
    @Code NVARCHAR (10) ,
    @Name NVARCHAR (255) ,
    @Contact NVARCHAR (255) ,
    @Address NVARCHAR (255) ,
    @City NVARCHAR (255) ,
    @Region NVARCHAR (80) ,
    @PostalCode VARCHAR (20) ,
    @IdCountry INT ,
    @Phone VARCHAR (40) ,
    @Mobile VARCHAR (40) ,
    @Id INT  output
)
AS
BEGIN
INSERT INTO "Supplier"
    (
        "Supplier"."Code",
        "Supplier"."Name",
        "Supplier"."Contact",
        "Supplier"."Address",
        "Supplier"."City",
        "Supplier"."Region",
        "Supplier"."PostalCode",
        "Supplier"."IdCountry",
        "Supplier"."Phone",
        "Supplier"."Mobile"
    )
    VALUES
    (
        @Code,
        @Name,
        @Contact,
        @Address,
        @City,
        @Region,
        @PostalCode,
        @IdCountry,
        @Phone,
        @Mobile
    );
    set @Id = SCOPE_IDENTITY();
END 
GO

De Insert stored procedure testen

We beginnen met het inserten van een leverancier met een Country Id die niet bestaat. Hiermee wil ik tonen wat een foreign key constraint in SQL precies betekent. We hebben het land met Country Id = 1 daarnet gedeleted. Als we nu een leverancier willen toegvoegen met IdCountry = 1 krijgen we een foutmelding omdat we een leverancier willen inserten met een Country Id die niet in de tabel Country bestaat.

We proberen opnieuw maar nu met een bestand Country Id:

Update

De Update stored procedure gelijkt op de Insert. Maar hier geef je de Id mee van de rij die je wilt updaten. Hier geldt ook de foreign key constraint. De IdCountry die je meegeeft moet in de tabel Country bestaan. Anders krijg je een foutmelding.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:02:23 AM
-- DML Update Stored Procedure for Supplier
-- 
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'SupplierUpdate'))
BEGIN
    DROP PROCEDURE SupplierUpdate
END
GO
CREATE PROCEDURE "SupplierUpdate"
(
	@Code NVARCHAR (10) ,
	@Name NVARCHAR (255) ,
	@Contact NVARCHAR (255) ,
	@Address NVARCHAR (255) ,
	@City NVARCHAR (255) ,
	@Region NVARCHAR (80) ,
	@PostalCode VARCHAR (20) ,
	@IdCountry INT ,
	@Phone VARCHAR (40) ,
	@Mobile VARCHAR (40) ,
	@Id INT 
)
AS
BEGIN
UPDATE "Supplier"
	SET
		"Code" = @Code,
		"Name" = @Name,
		"Contact" = @Contact,
		"Address" = @Address,
		"City" = @City,
		"Region" = @Region,
		"PostalCode" = @PostalCode,
		"IdCountry" = @IdCountry,
		"Phone" = @Phone,
		"Mobile" = @Mobile
	WHERE "Supplier"."Id" = @Id;
END 
GO

We testen de Update stored procedure en veranderen de IdCountry waarde;

Delete

We maken eerste de stored procedure:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:02:23 AM
-- DML Delete Stored Procedure for Supplier 
-- 
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'SupplierDelete'))
BEGIN
    DROP PROCEDURE SupplierDelete
END
GO
CREATE PROCEDURE "SupplierDelete"
(
	 @Id INT 
)
AS
BEGIN
DELETE FROM "Supplier"
	WHERE "Supplier"."Id" = @Id;
END 
GO

We testen de Delete stored procedure en we deleten de leverancier met Id = 1:

We voegen onze leverancier opnieuw toe:

exec SupplierInsert 'LEV00001AA', 'De Schoenmaker', 'Jan Jannsens', 'De Eerstestraat 1', 
     'Antwerpen', 'Antwerpen', '2000',      '2', '032458987', '0486665478', @Id output
print @Id
select * from Supplier

SelectOne

De SelectOne stored procedure is speciaal. We moeten immers niet alleen de IdCountry waarde ophalen maar ook de naam van het land. Daarvoor hebben we een inner join nodig om de tabel Supplier te linken aan de tabel Country. Let erop dat we alle kolommen laten voorafgaan door de naam van de tabel om abiguïteit te vermijden. De kolomnaam Name en Code bijvoorbeeld komen zowel voor in de tabel Country als in de tabel Supplier.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:02:23 AM
-- DML SelectOne Stored Procedure for Supplier 
-- 
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'SupplierSelectOne'))
BEGIN
    DROP PROCEDURE SupplierSelectOne
END
GO
CREATE PROCEDURE "SupplierSelectOne"
(
     @Id INT 
)
AS
BEGIN
SELECT "Supplier"."Code",
        "Supplier"."Name",
        "Supplier"."Contact",
        "Supplier"."Address",
        "Supplier"."City",
        "Supplier"."Region",
        "Supplier"."PostalCode",
        "Supplier"."IdCountry",
        "Country"."Name" as CountryName,
        "Supplier"."Phone",
        "Supplier"."Mobile"
    FROM "Supplier"
    INNER JOIN "Country" ON "Supplier"."IdCountry" = "Country"."Id"
    WHERE "Supplier"."Id" = @Id;
END 
GO

We testen de stored procedure:

SelectAll

We moeten in Mikmak logisch model om te gaan kijken welke kolommen getoond moeten worden. In de List kolom staat YES als de kolom in de SelectAll stored procedure moet worden opgenomen.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:44:53 AM
-- DML SelectAll Stored Procedure for table Supplier 
-- 
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'SupplierSelectAll'))
BEGIN
    DROP PROCEDURE SupplierSelectAll
END
GO
CREATE PROCEDURE "SupplierSelectAll"
AS
BEGIN
SELECT "Supplier"."Code",
    "Supplier"."Name",
    "Country"."Name" as "CountryName",
    "Supplier"."IdCountry",
    "Supplier"."Id"
    FROM "Supplier"
    INNER JOIN "Country"
        ON "Supplier"."IdCountry" = "Country"."Id"
    ORDER BY "Name";
END 
GO

We testen de SelectAll stored procedure uit:

SelectByCode

We maken eerste de stored procedure:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:44:53 AM
-- DML SelectByCode Stored Procedure for table Supplier
-- 
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'SupplierSelectByCode'))
BEGIN
    DROP PROCEDURE SupplierSelectByCode
END
GO
CREATE PROCEDURE "SupplierSelectByCode"
(
     @Code NVARCHAR (10) 
)
AS
BEGIN
SELECT "Supplier"."Code",
    "Supplier"."Name",
    "Country"."Name" as "CountryName",
    "Supplier"."IdCountry",
    "Supplier"."Id"

    FROM "Supplier"
    INNER JOIN "Country" as "Country"
        ON "Supplier"."IdCountry" = "Country"."Id"
    WHERE "Supplier"."Code" = @Code
    ORDER BY "Supplier"."Code";
END 
GO

En testen die vervolgens

exec SupplierSelectByCode 'LEV00001AA'

SelectByName

We maken eerst de stored procedure:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:44:53 AM
-- DML SelectByName Stored Procedure for table Supplier
-- 
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'SupplierSelectByName'))
BEGIN
    DROP PROCEDURE SupplierSelectByName
END
GO
CREATE PROCEDURE "SupplierSelectByName"
(
	 @Name NVARCHAR (255) 
)
AS
BEGIN
SELECT "Supplier"."Code",
	"Supplier"."Name",
	"IdCountry-Country"."Name" as "IdCountryName",
	"Supplier"."IdCountry",
	"Supplier"."Id"

	FROM "Supplier"
	INNER JOIN "Country" as "IdCountry-Country"
		ON "Supplier"."IdCountry" = "IdCountry-Country"."Id"
	WHERE "Supplier"."Name" = @Name
	ORDER BY "Supplier"."Name";
END 
GO

En testen die uit:

exec SupplierSelectByName 'De Schoenmaker'

SelectByContact

We maken de stored procedure:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:44:53 AM
-- DML SelectByContact Stored Procedure for table Supplier
-- 
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'SupplierSelectByContact'))
BEGIN
    DROP PROCEDURE SupplierSelectByContact
END
GO
CREATE PROCEDURE "SupplierSelectByContact"
(
     @Contact NVARCHAR (255) 
)
AS
BEGIN
SELECT "Supplier"."Code",
    "Supplier"."Name",
    "Country"."Name" as "CountryName",
    "Supplier"."IdCountry",
    "Supplier"."Id"

    FROM "Supplier"
    INNER JOIN "Country"
        ON "Supplier"."IdCountry" = "Country"."Id"
    WHERE "Supplier"."Contact" = @Contact
    ORDER BY "Supplier"."Contact";
END 
GO

En testen de stored procedure uit:

exec SupplierSelectByContact 'Jan Jannsens'

SelectByIdCountry

We beginnen met de stored procedure te maken:

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Thursday 7th of January 2016 11:44:53 AM
-- DML SelectByIdCountry Stored Procedure for table Supplier
--
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'SupplierSelectByIdCountry'))
BEGIN
DROP PROCEDURE SupplierSelectByIdCountry
END
GO
CREATE PROCEDURE "SupplierSelectByIdCountry"
(
@IdCountry INT
)
AS
BEGIN
SELECT "Supplier"."Code",
"Supplier"."Name",
"Country"."Name" as "CountryName",
"Supplier"."IdCountry",
"Supplier"."Id"

FROM "Supplier"
INNER JOIN "Country"
ON "Supplier"."IdCountry" = "Country"."Id"
WHERE "Supplier"."IdCountry" = @IdCountry;
END
GO

En testen de stored procedure uit:

exec SupplierSelectByIdCountry 3