11/9/18

Skriptet

USE minaAdresser;
GO
CREATE SCHEMA Tabeller;
GO
CREATE SCHEMA Adresser;
GO
CREATE TABLE Tabeller.Namn
 (
  NamnID int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Tabeller_Namn
   PRIMARY KEY CLUSTERED,
  Namn nvarchar(255) NOT NULL
   CONSTRAINT uq_Tabeller_Namn_Namn
   UNIQUE
 )
CREATE TABLE Tabeller.Adresser_Adress
 (
  AdressID  int IDENTITY(1,1) NOT NULL
   CONSTRAINT pk_Adresser_Adress
   PRIMARY KEY CLUSTERED,
  Förnamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Förnamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Efternamn int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Efternamn
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gata int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Gata
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID),
  Gatunummer nchar(15) NOT NULL
   CONSTRAINT df_Adresser_Adress_Gatunummer
   DEFAULT (''),
  Postnummer char(6) NOT NULL
   CONSTRAINT ck_Adresser_Adress_Gatunummer
   CHECK (Postnummer LIKE '[1-9][0-9][0-9] [0-9][0-9]'),
  Ort int NOT NULL
   CONSTRAINT fk_Adresser_Adress_Ort
   FOREIGN KEY REFERENCES Tabeller.Namn(NamnID)
 )
GO
CREATE VIEW Adresser.Adress
AS
SELECT
 a.AdressID,
 fn.Namn AS Förnamn,
 en.Namn AS Efternamn,
 g.Namn AS Gata,
 a.Gatunummer,
 a.Postnummer,
 o.Namn AS Ort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE VIEW Adresser.Utskrift
AS
SELECT
 a.AdressID,
 fn.Namn + ' ' + en.Namn    AS Mottagare,
 g.Namn + ' ' + a.Gatunummer   AS Postadress,
 a.Postnummer + ' ' + UPPER(o.Namn) AS Postort
FROM Tabeller.Adresser_Adress a
INNER JOIN Tabeller.Namn fn ON a.Förnamn = fn.NamnID
INNER JOIN Tabeller.Namn en ON a.Efternamn = en.NamnID
INNER JOIN Tabeller.Namn g ON a.Gata  = g.NamnID
INNER JOIN Tabeller.Namn o ON a.Ort  = o.NamnID
GO
CREATE FUNCTION Adresser.hämtaNamn(@NamnID int)
RETURNS nvarchar(255)
AS
BEGIN
RETURN(SELECT Namn FROM Tabeller.Namn WHERE NamnID = @NamnID)
END
GO
CREATE FUNCTION Adresser.hämtaNamnID(@Namn nvarchar(255))
RETURNS int
AS
BEGIN
RETURN(SELECT NamnID FROM Tabeller.Namn WHERE Namn = @Namn)
END
GO
CREATE PROCEDURE Adresser.skapaNamn @Namn nvarchar(255), @NamnID int OUTPUT
AS
BEGIN
DECLARE @Retur int = 0
IF @Namn IS NULL
BEGIN
 SET @Namn = ''
 SET @Retur += 10
END
SET @NamnID = Adresser.hämtaNamnID(@Namn)
IF @NamnID IS NULL
BEGIN
 INSERT INTO Tabeller.Namn(Namn) VALUES(@Namn)
 SET @NamnID = SCOPE_IDENTITY()
 SET @Retur += 1
END
RETURN @Retur
END
GO
CREATE PROCEDURE Adresser.uppdateraAdress
 @Förnamn nvarchar(255),
 @Efternamn nvarchar(255),
 @Gata nvarchar(255),
 @Gatunummer nvarchar(255),
 @Postnummer nvarchar(255),
 @Ort nvarchar(255),
 @AdressID int OUTPUT,
 @updateAdressID int = -1
AS
BEGIN
DECLARE
 @Retur int = 0,
 @Exec int = 0,
 @FörnamnID int,
 @EfternamnID int,
 @GataID int,
 @OrtID int
IF @Postnummer NOT LIKE '[1-9][0-9][0-9] [0-9][0-9]' SET @Retur = -3
ELSE IF @Gatunummer IS NULL SET @Retur = -2
ELSE
BEGIN
EXEC @Exec = Adresser.skapaNamn @Förnamn, @FörnamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Efternamn, @EfternamnID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 1000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Gata, @GataID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 100000
 SET @Exec = 0
END
EXEC @Exec = Adresser.skapaNamn @Ort, @OrtID OUTPUT
IF @exec != 0
BEGIN
 SET @Retur += @Exec * 10000000
 SET @Exec = 0
END
END
IF @Retur >= 0
BEGIN
IF @updateAdressID = -1
BEGIN
SELECT @AdressID = AdressID FROM Tabeller.Adresser_Adress
WHERE
 Förnamn = @FörnamnID
AND Efternamn = @EfternamnID
AND Gata = @GataID
AND Gatunummer = @Gatunummer
AND Postnummer = @Postnummer
AND Ort = @OrtID
IF @AdressID IS NULL
BEGIN
INSERT INTO Tabeller.Adresser_Adress(Förnamn, Efternamn, Gata, Gatunummer, Postnummer, Ort)
VALUES(@FörnamnID, @EfternamnID, @GataID, @Gatunummer, @Postnummer, @OrtID)
SET @AdressID = SCOPE_IDENTITY()
SET @Retur += 1
END
END
ELSE
BEGIN
DECLARE @updated TABLE (AdressID int)
UPDATE Tabeller.Adresser_Adress
SET
 Förnamn = @FörnamnID,
 Efternamn = @EfternamnID,
 Gata = @GataID,
 Gatunummer = @Gatunummer,
 Postnummer = @Postnummer,
 Ort = @OrtID
OUTPUT inserted.AdressID INTO @updated
WHERE AdressID = @updateAdressID
SELECT @AdressID = AdressID FROM @updated WHERE AdressID = @updateAdressID
SET @Retur += 2
END
END
RETURN @Retur
END
GO
USE master

No comments:

Post a Comment