Skapa en databas för recept i SQL

Jag försöker alltid effektivisera mitt arbete, så att jag får så mycket som möjligt gjort på kortast möjliga tid. Det är enormt viktigt för mig. Därför brukar jag kombinera arbeten, så att det ena får det andra gjort. Även om jag tycker om att koppla av framför en bra film en stund, känns det ibland frustrerande att slösa för mycket tid på det. Då passar jag på att göra andra saker samtidigt, som att vika tvätt. När jag lär mig programmering letar jag efter liknande lösningar.

Eftersom jag har ett stort intresse av att skapa nyttiga och goda recept, har jag valt att min första databas i SQL ska bli min receptsamling. Antagligen hade det varit betydligt enklare att skapa en databas med författare och deras romaner, men den har jag ingen nytta av senare. Fördelen med att ge sig i kast med ett verkligt problem i praktiken, är att man lättare förstår vilka metoder som fungerar eller inte.

Det som är problematiskt, är inte att skapa databasen med tillhörande tabeller och fält i språket SQL. Det svåra är att komma fram till vilka tabeller som ska finnas, vilka fält var och en ska ha och hur de ska relatera till varandra.

Jag börjar med att skapa fyra tabeller där första fältet i alla är en primärnyckel, för att vara säker på att kunna skilja på de olika raderna. Först vill jag ha tabellen ”Recept” där jag lägger in namnet på rätten, t ex ”Suzannes Cheesecake”. Nästa tabell som ska finnas är ”Receptgrupp” där jag har namnet på typ av rätt, t ex ”Dessert”. Primärnyckeln i den tabellen ska relatera till en främmande nyckel i tabellen ”Recept”. Sedan vill jag ha tabellen ”Livsmedel” där jag samlar alla råvaror som jag kommer att ha med i mina recept, t ex ”Grädde” eller ”Philadelphiaost”. I tabellen ”LivsmedelGrupp” finns typen av livsmedel, t ex ”Mjölkprodukter”. Primärnyckeln i den tabellen ska relatera till en främmande nyckel i tabellen ”Livsmedel”.

Databasdiagram
Från början tänkte jag också att tabellen ”Recept” skulle relatera till tabellen ”Livsmedel” med ”LivsmedelId” som främmande nyckel, men det var ingen bra idé.

Med dessa fyra tabeller har jag den uppsättning av tabeller och den information som jag vet att jag behöver. Om vi tar ”Suzannes Cheesecake” som exempel, så har den sin plats i tabellen ”Recept” och den relaterar till tabellen Receptgrupp och där finns ”Dessert” som en receptgrupp. Sedan finns råvarorna ”Grädde” och ”Philadelphiaost” i tabellen  ”Livsmedel” och eftersom den tabellen också relaterar till tabellen ”Recept”, med den främmande nyckeln ”LivsmedelId”, kan jag hämta de råvaror jag behöver från tabellen ”Livsmedel” genom att först leta upp ”Grädde”, ta värdet på ”LivsmedelId” för den raden och lägga in det värdet i ”LivsmedelId” i tabellen ”Recept”.

Det är här det blir fel, eftersom jag måste hämta ”LivsmedelId” för både ”Grädde” och ”Philadelphiaost” och dessa två värden kan inte läggas in i fältet ”LivsmedelId” i tabellen ”Recept”. Det beror på att varje ruta bara kan lagra ett värde, s k enkelt värde. Därför tar jag bort den främmande nyckeln ”LivsmedelId” i tabellen ”Recept” och därmed bryter jag förbindelsen mellan tabellerna ”Recept” och ”Livsmedel”. Därför måste jag skapa ytterligare en tabell, som ska vara en slags mellanhand, mellan dessa. Jag tänkte något i stil med detta:

diagram
Länken mellan tabellerna ”Recept” och ”Livsmedel” är borttagen och har ersatts med ytterligare en tabell som jag kallar ”Ingredienser”. I den finns det två främmande nycklar, en som hämtar värdet från primärnyckeln i tabellen ”Recept” och en som hämtar värdet från primärnyckeln i tabellen ”Livsmedel”.

Nu har vi alltså dessa tabeller:

  1. Recept – Primärnyckeln ”ReceptId”. Namnet på rätten i ”ReceptNamn”. Främmande nyckeln ”ReceptGruppId” som hämtar värdet från primärnyckeln i tabellen ”ReceptGrupp” och därmed fås information om vilken typ av rätt det är.
  2. ReceptGrupp – Primärnyckeln ”ReceptGruppId” som bestämmer värdet i den främmande nyckeln i tabellen ”Recept”. Namnet på typen av rätt i ”ReceptGruppNamn”.
  3. Livsmedel – Primärnyckeln ”LivsmedelId”. Namnet på livsmedlet i ”LivsmedelID”. Främmande nyckeln ”LivsmedelGruppId” som hämtar värdet från primärnyckeln i tabellen ”LivsmedelGrupp” och därmed fås information om vilken livsmedelsgrupp det är.
  4. LivsmedelGrupp – Primärnyckeln ”LivsmedelGruppId” som bestämmer värdet i den främmande nyckeln i tabellen ”Livsmedel”. Namnet på gruppen av livsmedel i ”LivsmedelGruppNamn”.
  5. Ingredienser – Främmande nyckeln ”ReceptId” som hämtar värdet från primärnyckeln i tabellen ”Recept”. Främmande nyckeln ”LivsmedelId” som hämtar värdet från primärnyckeln i tabellen ”Livsmedel”. Mängden av ingrediensen i ”Vikt”.

Datatyper

Samtliga numeriska fält kommer enbart kunna lagra ett heltal, så de kommer således att vara av typen INT.  Antagligen skulle det räcka med att dessa fält är av datatypen SMALLINT, men jag håller mig till INT innan jag har fördjupat mig i datatyper. För textfälten står valet mellan VARCHAR och NVARCHAR. Eftersom jag inte har tid att fastna i ytterligare en detalj, utgår jag helt sonika från att NVARCHAR är det bästa alternativet tills jag har mer kött på benen. Det fungerar trots allt med vilken som.

Skapa databasen med tillhörande tabeller

Skapa databasen:

CREATE DATABASE Recept;

Sen talar jag om att jag ska använda databasen jag nyss skapade:

USE Recept;

I SQL Server Management Studio, kan jag i stället välja databas i rullgardinsmenyn ”Available databases”, men jag rekommenderar att man använder ovanstående metod. Då slipper man göra samma misstag som jag gjorde i början. Jag modifierade fel databas, i tron att rätt var vald.

Första tabellen: LivsmedelGrupp

Det är viktigt att skapa tabellerna i rätt ordning. Det går t ex inte att skapa en tabell med en främmande nyckel till en tabell som inte är skapad ännu. Tabellerna ”LivsmedelGrupp” och ”ReceptGrupp” saknar främmande nycklar, så det går att börja med vilken som helst av dessa två. När de är skapade fungerar det att skapa tabellerna ”Livsmedel” och ”Recept” som har en främmande nyckel som refererar till primärnyckeln i ”LivsmedelGrupp” respektive ”ReceptGrupp”.  Tabellen ”Ingredienser” måste skapas sist eftersom den har två främmande nycklar som refererar till tabellerna ”Recept” och ”Livsmedel”.

I tabellerna ”Livsmedel”, ”LivsmedelGrupp”, ”Recept” och ”ReceptGrupp” insåg jag att jag vill ha UNIQUE på fälten för namn, för att inte riskera att skriva in några onödiga dubbletter. Visserligen kan ett recept på en maträtt förekomma många gånger, med lite variationer. Då vill jag ha med det i namnet så att alla receptnamn ändå blir unika, t ex ”Morfars köttfärssås” och ”Suzannes köttfärssås”. Jag kommer också att använda IDENTIY(1,1), som sköter den unika uppräkningen av värden i fälten med primärnycklar, i dessa fyra tabeller.

CREATE TABLE LivsmedelGrupp(
    LivsmedelGruppId int NOT NULL IDENTITY(1,1),
    LivsmedelGruppNamn nvarchar(100) UNIQUE, 
    CONSTRAINT PK_LivsmedelGrupp PRIMARY KEY(LivsmedelGruppId)
);

I den här tabellen är det inga konstigheter. Bara fältet med primärnyckeln och fältet för namn på livsmedelsgrupper. Primärnyckeln kommer att relatera till  den främmande nyckeln i tabellen ”Livsmedel” nedan.

Andra tabellen: Livsmedel

Jag lägger till IDENTITY i primärnyckeln och UNIQUE i fältet ”LivsmedelNamn”. Annars är det precis som illustrationen ovan.

CREATE TABLE Livsmedel(
    LivsmedelId int NOT NULL IDENTITY(1,1),
    LivsmedelNamn nvarchar(100) UNIQUE, 
    LivsmedelGruppId int NOT NULL,
    CONSTRAINT PK_Livsmedel PRIMARY KEY(LivsmedelId),
    CONSTRAINT FK_Livsmedel_LivsmedelGrupp FOREIGN KEY(LivsmedelGruppId)
    REFERENCES LivsmedelGrupp(LivsmedelGruppId)
);

Egentligen är det inget märkvärdigt i den här tabellen heller. Den är ganska lik den förra tabellen, med ett fält för id och ett för namn för livsmedel. Det som är extra här, är den främmande nyckeln som relaterar till primärnyckeln i tabellen ”LivsmedelGrupp” där värdet inhämtas.

Tredje tabellen: ReceptGrupp

Även den här tabellen är som illustrationen ovan, bortsett från tillägget av IDENTITY och UNIQUE.

CREATE TABLE ReceptGrupp(
    ReceptGruppId int NOT NULL IDENTITY(1,1),
    ReceptGruppNamn nvarchar(100) UNIQUE, 
    CONSTRAINT PK_ReceptGrupp PRIMARY KEY(ReceptGruppId)
);

Här är ytterligare en enkel tabell med id och namn på receptgrupper, t ex dessert, varmrätt och så vidare. Primärnyckeln kommer att relatera till den främmande nyckeln i tabellen ”Recept”.

Fjärde tabellen: Recept

Förutom IDENTITY och UNIQUE, kommer jag också att komplettera med fältet ”Beskrivning”, annars blir det ju inget komplett recept. Då uppstår frågan vilken datatyp som jag ska använda. Jag har redan kommit fram till att jag ska använda datatypen NVARCHAR för textfält. När det gäller beskrivningen måste jag dock tänka på att den kommer innehålla betydligt fler tecken än namnet på t ex ett recept eller en ingrediens. Med datatypen NVARCHAR kan vi lagra 4000 tecken, så om beskrivningen är längre än det måste vi använda NVARCHAR(MAX).  De flesta av mina recept har en beskrivning med 1000 tecken, vilket innebär att NVARCHAR ganska troligt räcker för mina behov. Dessutom är det inga stora problem att ändra datatypen till en som kan lagra mer än innan. Därför väljer vi NVARCHAR här också och för säkerhets skull 4000 tecken.

CREATE TABLE Recept(
    ReceptId int NOT NULL IDENTITY(1,1),
    ReceptNamn nvarchar(100) UNIQUE,
    Beskrivning nvarchar(4000),
    ReceptGruppId int NOT NULL,
    CONSTRAINT PK_Recept PRIMARY KEY(ReceptId),
    CONSTRAINT FK_Recept_ReceptGrupp FOREIGN KEY(ReceptGruppId)
    REFERENCES ReceptGrupp(ReceptGruppId)
);

Även här finns id och namn på recepten. Sen den främmande nyckeln som relaterar till primärnyckeln i tabellen ”Receptgrupp”, där värdet inhämtas.

Femte tabellen: Ingredienser

Eftersom inga av fälten i tabellen ”Ingredienser” kommer att vara unika, kan de inte vara primärnyckel. Däremot är det troligt att båda fälten tillsammans kommer att vara unika. Man kan dock inte ha mer än en primärnyckel per tabell, utan måste skapa en primärnyckel för flera kolumner. Även om dessa två fält oftast kommer att vara unika, finns det en möjlighet att det blir en krock. Ett recept på våfflor kan t ex innehålla grädde både i våffelsmeten och som tillbehör. Men att skriva en ingrediens två gånger i ett recept verkar dumt. Då är det bättre att antingen skriva den totala mängden grädde i ingredienslistan och dela upp det i beskrivningen eller använda sig av underrubriker.

Även om jag inte har med det i illustrationen ovan, ska vi låta fälten ”ReceptId” och LivsmedelId” i tabellen ”Ingredienser” vara en primärnyckel. Dessutom ska vi lägga till fältet ”underrubriker”, där vi kan fylla i vilken underrubrik ingrediensen tillhör, t ex ”Sås” eller ”Tillbehör”. Eftersom vissa ingredienser som t ex ägg räknas i antal snarare än i vikt, ska vi också lägga till fältet ”Antal”. Det innebär att vi antingen fyller i vikt eller antal för en ingrediens.

Ibland skriver man varken antal eller vikt för en ingrediens och därför kan både ”Vikt” och ”Antal” ha ett NULL-värde.

Obs. Jag ska justera nedanstående senare, eftersom jag tycker att det lämpar sig att fältet ”Underrubriker” får standardvärdet ”Main” när det inte finns några underrubriker som ”Tillbehör” och ”Sås”. Visserligen går dessa fält att särskilja med operatorn IS NULL, men det känns mer korrekt att använda DEFAULT.

CREATE TABLE Ingredienser(
    ReceptId int NOT NULL,
    LivsmedelId int NOT NULL,
    Vikt int, 
    Antal int,
    Underrubriker nvarchar(100)
    CONSTRAINT PK_Ingredienser PRIMARY KEY (ReceptId, LivsmedelId),
    CONSTRAINT FK_Recept_Ingredienser FOREIGN KEY(ReceptId)
    REFERENCES Recept(ReceptId),
    CONSTRAINT FK_Livsmedel_Ingredienser FOREIGN KEY(LivsmedelId)
    REFERENCES Livsmedel(LivsmedelId)
);

Finns det någon nackdel att använda dessa två kolumner som en primärnyckel? Borde jag i stället ha skapat fältet ”IngrediensId” och deklarerat den kolumnen som primärnyckel? Som jag har förstått det ska man helst inte använda främmande nycklar som primärnycklar, men just i det här fallet känns det som att det är en bra lösning. Så jag följer magkänslan.

Fylla databasen med innehåll

Givetvis måste jag fylla tabellerna ”Livsmedel”, ”LivsmedelGrupp” och ”ReceptGrupp”, eftersom dessa behövs när jag ska lägga in mina recept. För att göra det använder jag mig av uttrycket INSERT INTO.

Fylla tabellen ReceptGrupp

INSERT INTO ReceptGrupp 
    (ReceptGruppNamn) 
VALUES
    ('Mellanmål'), ('Dessert'), ('Förrätt'), ('Varmrätt'), ('Sallad'), ('Godis och Snacks');

Att lägga in det här innehållet var inga svårigheter.

Fylla tabellen LivsmedelGrupp

INSERT INTO LivsmedelGrupp 
    (LivsmedelGruppNamn) 
VALUES
    ('Fisk och skaldjur'), ('Fläskkött'), ('Nötkött'), ('Fågel'), ('Ägg'), ('Mejeriprodukter'), 
    ('Spannmål och ris'), ('Frukt'), ('Smör och matolja'), ('Godis och snacks'), ('Svamp'), 
    ('Drycker och Alkohol'), ('Grönsaker'), ('Bönor och baljväxter'), ('Kryddor'), ('Potatis och rotfrukter');

Även det här innehållet var lätt att lägga in. Jag behövde inte ta hänsyn till relationen med andra tabeller varken för tabellen ”ReceptGrupp” eller ”LivsmedelGrupp”. Riktigt så enkelt är det inte att lägga in råvaror i tabellen ”Livsmedel”. Då måste jag nämligen leta upp rätt livsmedelsgrupp i tabellen ”LivsmedelGrupp” och hämta värdet i ”LivsmedelGruppId” på samma rad. Detta värde ska sedan läggas in i  den främmande nyckeln ”LivsmedelGruppId” i tabellen ”Livsmedel”. Då går det inte använda ovanstående metod. I stället måste jag använda uttrycket INSERT INTO SELECT. Det innebär att jag lägger in data i en tabell, samtidigt som jag kopierar data från en annan.

Fylla tabellen Livsmedel

INSERT INTO Livsmedel 
    (LivsmedelNamn, LivsmedelGruppId) 
SELECT
    'Makrillfilé', LivsmedelGruppId
FROM
    LivsmedelGrupp
WHERE
    LivsmedelGruppNamn = 'Fisk och Skaldjur';

Jag fyllde på med några livsmedel till inom olika livsmedelsgrupper, så att det finns något att arbeta med. Nu när jag har fyllt på med innehåll i båda tabellerna, kan jag hämta en lista med livsmedel och se vilken livsmedelsgrupp de tillhör.

SELECT 
    Livsmedel.LivsmedelNamn,
    LivsmedelGrupp.LivsmedelGruppNamn
FROM 
    Livsmedel
    INNER JOIN LivsmedelGrupp
    ON Livsmedel.LivsmedelGruppId = LivsmedelGrupp.LivsmedelGruppId;

 

Select Resultat

 

Till vänster listas alla livsmedel och till höger visas korrekt livsmedelsgrupp för dem. Resultatet blev precis som jag förväntade mig.

 

 

 

 

Fylla tabellen Recept

För att lägga in en maträtt i tabellen ”Recept” använder jag också uttrycket INSERT INTO SELECT. Jag letar upp rätt typ av rätt i tabellen ”ReceptGrupp” och hämtar värdet i ”ReceptGruppId” på samma rad. Detta värde ska sedan läggas in i den främmande nyckeln ”ReceptGruppId” i tabellen ”Recept”. Alltså, jag lägger in data i en tabell, samtidigt som jag kopierar data från en annan.

INSERT INTO Recept 
    (ReceptNamn, ReceptGruppId) 
SELECT
    'Suzannes Cheesecake', ReceptGruppId
FROM
    ReceptGrupp
WHERE
    ReceptGruppNamn = 'Dessert';

Nu kan vi lista maträtter tillsammans med vilken typ av rätt de tillhör.

select 
    recept.receptnamn,
    receptgrupp.receptgruppnamn 
from 
    recept 
    inner join receptgrupp
    on recept.receptgruppId = receptgrupp.receptgruppid;

Fylla tabellen Ingredienser

För att fylla på med ingredienser till varje recept, utgår jag från att jag ska lägga in 200 g Philadelphiaost som ingrediens till ”Suzannes Cheesecake” och även här använder jag uttrycket INSERT INTO SELECT. Jag ska alltså lägga in värden i fälten ”ReceptId”, ”LivsmedelId” och ”Vikt” (om jag hade lagt in ägg, hade jag i stället valt fältet ”Antal” )  i tabellen ”Ingredienser”. För att få fram vilka värden som ska in i dessa fält, måste vi välja ”ReceptId” i tabellen ”Recept”, ”LivsmedelID” i tabellen ”Livsmedel” och för fältet ”Vikt” skriver vi helt enkelt ”200”.  Eftersom receptet Cheesecake inte kommer att ha några underrubriker och eftersom Philadephiaost inte räknas i antal, läggs inget in i fälten ”Antal” eller ”Underrubriker” den här gången.

INSERT INTO Ingredienser
    (ReceptId, LivsmedelID, Vikt) -- Fält att lägga in värden i.
SELECT -- De tre värdena som ska kopieras till de tre fälten i tabellen "Ingredienser".
    Recept.ReceptId, 
    Livsmedel.LivsmedelId,
    '200'
FROM -- Tabellerna att hämta värdena från.
    Recept,
    Livsmedel 
-- Hämtar värdet för "ReceptId" i tabellen "recept" på samma rad där "Suzannes Cheesecake" = "ReceptNamn"
-- och värdet för "LivsmedelID" i tabellen "Livsmedel" på samma rad där 
WHERE "Philadelphiaost" = "LivsmedelNamn".
   Recept.ReceptNamn = 'Suzannes Cheesecake' AND Livsmedel.LivsmedelNamn = 'Philadelphiaost';

För att kontrollera att allt fungerar som det ska skriver vi det här:

SELECT
    Recept.ReceptNamn, 
    Livsmedel.LivsmedelNamn,
    Ingredienser.Vikt
FROM
    Recept
    INNER JOIN Ingredienser
    ON Recept.ReceptId = Ingredienser.ReceptId,
    Livsmedel 
WHERE
    Livsmedel.LivsmedelId = Ingredienser.LivsmedelId;

Eftersom rätterna listas med tillhörande ingredienser och vikt, fungerar det som det ska.

delresultat
Här listas de tre kolumnerna med recept, ingredienser och vikt.

 

Nu finns nog allt jag behöver i min receptdatabas. Det är endast underrubriker och tillhörande tabeller som saknas. men det får bli nästa gång.

TRY…CATCH

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[ ; ]

Blocken TRY…CATCH används för felhantering. Det går ut på att man samlar alla uttryck eller satser innanför ett block med TRY och gör ett försök att köra dem. Efter det blocket har man alltid ett block med CATCH och koden där körs om något går fel i försöket. Innanför CATCH-blocket lägger man bl a ROLLBACK TRANSACTION när man använder transaktioner och systemfunktioner som ERROR_MESSAGE().

TRY

För att lägga till felhantering behöver du tala om för SQL-server att du vill försöka köra lite kod och det gör du genom att börja med satsen BEGIN TRY. Sedan skriver du de satser som du vill testa och när du är klar med försöket avslutar du med satsen END TRY.

BEGIN TRY
    BEGIN TRANSACTION AddPerson 
        INSERT INTO Personer
            (Name, Phone, City)
        VALUES
            ('Suzanne Apelqvist', '070-333 222 11', 'Örnsköldsvik')
    COMMIT TRANSACTION AddPerson 
    PRINT 'Personen är tillagd'
END TRY

CATCH

Ett block med TRY fyller ingen större funktion utan ett block med CATCH.  Med CATCH fångar du upp alla fel som uppstår i försöket. Mellan BEGIN CATCH och END CATCH skriver du instruktioner om vad som ska ske när det blir fel. I nedanstående exempel har jag använt mig av systemfunktionen ERROR_MESSAGE(), som skriver ut ett tydligare meddelande om vad som gick fel och ROLLBACK TRANSACTION för att ångra händelserna i transaktionen. Fler systemfunktioner för felhantering finner du längre ner på sidan.

BEGIN CATCH
    ROLLBACK TRANSACTION AddPerson
    select ERROR_MESSAGE() AS FelMeddelande;
END CATCH

TRY…CATCH

Här är ett exempel med båda blocken TRY…CATCH.

BEGIN TRY
    BEGIN TRANSACTION AddPerson 
        INSERT INTO Personer
            (Name, Phone, City)
        VALUES
            ('Suzanne Apelqvist', '070-333 222 11', 'Örnsköldsvik')
    COMMIT TRANSACTION AddPerson 
    PRINT 'Personen är tillagd'
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION AddPerson
    select ERROR_MESSAGE() AS FelMeddelande;
END CATCH

Satsen innanför blocket BEGIN TRY och END TRY kommer att utföras om inget är fel. Annars körs satsen innanför blocket BEGIN CATCH och END CATCH.

Systemfunktioner

  • ERROR_NUMBER()
    Returnerar antalet fel.
  • ERROR_SEVERITY()
    Returnerar the severity.
  • ERROR_STATE()
    Returnerar the error state number.
  • ERROR_PROCEDURE()
    Returnerar namnet på den lagrade proceduren eller trigger där felet uppstod.
  • ERROR_LINE()
    Returnerar radnumret inuti rutinen som orsakade felet.
  • ERROR_MESSAGE()
    Returnerar hela texten för felmeddelandet.

Transaktioner

Transaktioner är händelser som ändrar data i databasen och alla dessa händelser registreras i transaktionsloggen. Om man t ex kör ett skript med någon av satserna UPDATE, DELETE eller INSERT, sker transaktionen direkt och det går inte att ångra. Det är dock möjligt att styra när transaktioner äger rum, genom att ge SQL-servern mer information än ovanstående satser. Alla ändringar efter BEGIN TRANSACTION kan ångras, men efter COMMIT TRANSACTION blir ändringarna permanenta. Om det uppstår några fel, kan alla ändringar raderas med satsen ROLLBACK TRANSACTION.

BEGIN TRANSACTION

BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ ‘description’ ] ]
]
[ ; ]

För att kunna kontrollera när transaktionen äger rum måste man först tala om för SQL-servern när den börjar. Då kommer inte efterföljande kod att köras automatiskt. Det går även bra att använda den kortare varianten TRAN i stället för nedanstående TRANSATION.

BEGIN TRANSACTION AddPerson
    INSERT INTO Personer
        (Name, Phone, City)
    VALUES
        ('Suzanne Apelqvist', '070-333 22 11', 'Örnsköldsvik')

Här kommer ingenting att skrivas till databasen.

Det går att namnge transaktionerna som jag har gjort ovan med ”AddPerson”. Detsamma gäller för nedanstående COMMIT och ROLLBACK. Det är en fördel att namnge transaktioner när de innehåller många händelser. Använd bara transaktionsnamn för de yttre paren av nästlade satserna BEGIN…COMMIT eller BEGIN…ROLLBACK. Observera också att transaktionsnamn är känsliga för små och stora bokstäver, även om instansen inte är det.

Om inte en plats i transaktionen har angetts för att spara eller om inget transaktionsnamn har angetts, ångras hela transaktionen. Vid nästlade transaktioner ångras alla inre transaktioner till den yttre satsen BEGIN TRANSACTION.

COMMIT

COMMIT [ { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
[ ; ]

När du är säker på att du inte kommer att ångra dig och händelserna ska sparas permanent i databasen, avslutar du med COMMIT TRANSACTION. Transaktionsnamn ignoreras och är bara en hjälp för programmeraren. Inre nästlade COMMIT-satser frigör inte resurser och gör inte ändringar permanenta. Det sker först när du använder COMMIT-satsen i den yttre transaktionen.

BEGIN TRANSACTION AddPerson
    INSERT INTO Personer
        (Name, Phone, City)
    VALUES
        ('Suzanne Apelqvist', '070-333 22 11', 'Örnsköldsvik')
COMMIT TRANSACTION

Här kommer en rad skrivas till databasen.

Ångra en händelse

Fördelen med att använda BEGIN TRANSACTION är att man kan ångra sig. Det görs genom att skriva ROLLBACK TRANSACTION. För att se att raden verkligen läggs till och raderas, har jag med en SELECT-sats före och efter.

BEGIN TRANSACTION
INSERT INTO Personer
    (Name,Phone, City)
VALUES
    ('Suzanne Apelqvist', '070-333 22 11', 'Örnsköldsvik')

SELECT * FROM Personer WHERE Name = 'Suzanne Apelqvist'

ROLLBACK TRANSACTION

SELECT * FROM Personer WHERE Name = 'Suzanne Apelqvist'

Flödeskontroll

Den riktiga kraften med ROLLBACK är inte att manuellt välja om den ska äga rum eller inte, utan genom att använda flödeskontroll. Det går t ex att använda villkorssatsen IF…ELSE för det, även om det inte är den bästa metoden.

Felhantering

Det absolut bästa att använda när du vill styra transaktioner är felhantering med blocken TRY…CATCH. Om det uppstår ett fel och vi bara använder en enkel transaktion som avslutas med COMMIT TRANSACTION, kommer SQL-server automatiskt ångra hela transaktionen och generera ett felmeddelande. Även om det fungerar är det bättre att styra felhanteringen så att vi får lite mer användbar information.

TRY

För att lägga till felhantering behöver du tala om för SQL-server att du vill försöka köra lite kod och det gör du genom att börja med satsen BEGIN TRY. Sedan skriver du de satser som du vill testa och när du är klar med försöket avslutar du med satsen END TRY.

BEGIN TRY
    BEGIN TRANSACTION AddPerson 
        INSERT INTO Personer
            (Name, Phone, City)
        VALUES
            ('Suzanne Apelqvist', '070-333 222 11', 'Örnsköldsvik')
    COMMIT TRANSACTION AddPerson 
    PRINT 'Personen är tillagd'
END TRY

CATCH

Ett block med TRY fyller ingen större funktion utan ett block med CATCH.  Med CATCH fångar du upp alla fel som uppstår i försöket. Mellan BEGIN CATCH och END CATCH skriver du instruktioner om vad som ska ske när det blir fel. Här placeras också satsen ROLLBACK TRANSACTION, eftersom vi givetvis inte vill att ändringarna ska skrivas till databasen när de innehåller fel.

BEGIN CATCH
    ROLLBACK TRANSACTION AddPerson 
    PRINT 'Personen finns redan'
END CATCH

TRY…CATCH

BEGIN TRY
    BEGIN TRANSACTION AddPerson 
        INSERT INTO Personer
            (Name, Phone, City)
        VALUES
            ('Suzanne Apelqvist', '070-333 222 11', 'Örnsköldsvik')
    COMMIT TRANSACTION AddPerson 
    PRINT 'Personen är tillagd'
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION AddPerson 
    PRINT 'Personen finns redan'
END CATCH

Satsen innanför blocket BEGIN TRY och END TRY kommer att utföras om inget är fel. Annars körs satsen innanför blocket BEGIN CATCH och END CATCH.

Nästlade transaktioner

Det går att ha fler transaktioner inuti varandra.

BEGIN TRANSACTION Name1
    PRINT 'Huvudtransaktionen'
    BEGIN TRANSACTION Name2
        PRINT 'En nästlad transaktion'
    COMMIT TRANSACTION Name2
COMMIT TRANSACTION Name1

Här skrivs ”Huvudtransaktionen” från den yttre transaktionen ut och ”En nästlad transaktion” från den inre.

För att se hur många transaktioner som finns i en transaktion, kan man använda PRINT tillsammans med systemfunktionen @@TRANSCOUNT. Varje gång du börjar en transaktion ökar antalet transaktioner med ett och efter varje COMMIT minskar antalet med ett.

BEGIN TRANSACTION Name1
    PRINT @@TRANCOUNT
    BEGIN TRANSACTION Name2 
        PRINT @@TRANCOUNT
    COMMIT TRANSACTION Name2
    PRINT @@TRANCOUNT
COMMIT TRANSACTION Name1

Här blir resultatet i de tre PRINT-satserna 1, 2 och slutligen 1.

I exemplet ovan skriver den första PRINT-satsen ut 1 eftersom en transaktion har påbörjats. Andra PRINT-satsen skriver ut 2 eftersom två transaktioner har påbörjats, den yttre och den inre. I den tredje PRINT-satsen blir det plötsligt 1 igen. Det beror på att efter COMMIT har den inre transaktionen slutförts och då räknas den inte längre.

I nästlade transaktioner kan man inte använda namn för de inre transaktionerna i satsen ROLLBACK. Med satsen ROLLBACK ångrar du inte bara de inre transaktionerna utan alla ändras, ända tillbaka till den yttre. Antalet transaktioner i den sista PRINT-satsen, alltså efter ROLLBACK, kommer därför visa 0.

BEGIN TRANSACTION Name1
    PRINT @@TRANCOUNT
    BEGIN TRANSACTION Name2
        PRINT @@TRANCOUNT
    ROLLBACK TRANSACTION -- Här har jag tagit bort namnet på den inre transaktionen.
    PRINT @@TRANCOUNT
COMMIT TRANSACTION Name1

Här blir resultatet i de tre PRINT-satserna 1, 2 och slutligen 0. Dessutom blir det ett felmeddelandet.

I ovanstående exempel dyker felmeddelandet ”The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION” upp. Anledningen är att ROLLBACK ångrar alla transaktioner och då finns det inga transaktioner kvar att slutföra.

Om du i stället för ROLLBACK, avslutar den inre transaktionen med COMMIT och byter avslutar den yttre transaktionen med ROLLBACK, blir det inget felmeddelande. Det innebär dock inte att händelserna i den inre transaktionen har sparats, eftersom ROLLBACK i den yttre drar tillbaka allt som har hänt i hela transaktionen.

Den här informationen får mig att fundera på om det finns någon mening med COMMIT och ROLLBACK i inre transaktioner eller med nästlade transaktioner överhuvudtaget?

Savepoints

Det går att ångra bara vissa delar av transaktioner. Det görs genom att i stället för att börja transaktionen med BEGIN TRANSACTION använda SAVE TRANSACTION. Som jag nämnde tidigare är det inte tillåtet med namn för de nästlade transaktionerna i satsen ROLLBACK, men det gäller inte för savepoints. Då måste namnet vara. Savepoints börjar inte en ny transation, utan sparar alla händelser fram till den punkten. Så om du använder ROLLBACK efter en savepoint, så ångras allt tillbaka till den senaste sparade punkten.

BEGIN TRANSACTION Name1
    PRINT @@TRANCOUNT
    SAVE TRANSACTION Name2
        PRINT @@TRANCOUNT
    ROLLBACK TRANSACTION Name2 -- Här måste namnet på vara med.
    PRINT @@TRANCOUNT
COMMIT TRANSACTION Name1

Här blir resultatet i de tre PRINT-satserna 1, 1 och slutligen 1.

Resultatet i första PRINT-satsen i ovanstående exempel blir 1. Att det även blir 1 i den andra PRINT-satsen beror på att SAVE TRANSACTION inte börjar en ny transaktion och sedan ligger PRINT-satsen före ROLLBACK. Eftersom ROLLBACK bara ångrar alla händelser från och med den senaste punkten som sparades, är det fortfarande en transaktion kvar. Därför blir resultatet i första PRINT-satsen 1.

Kort om savepoints i lagrade procedurer

Om du börjar en transaktion i en procedur och sedan anropar en annan procedur, kommer transaktionen hållas öppen. Du kan skapa en savepoint inuti den anropade proceduren för att kunna ångra vilka händelser som helst som utförs inuti den.

Underfråga (Subquery)

En underfråga eller nästlad fråga är en fråga inuti en annan och den är ofta inbäddad i en WHERE-sats. Den måste inneslutas av ett par parenteser. Underfrågor används för att returnera data som kommer att användas i huvudfrågan som ett villkor för att ytterligare begränsa data som ska visas. Satserna SELECT, INSERT, UPDATE och DELETE, kan användas med underfrågor, tillsammans med operatorerna like =, <, >, >=, <=, IN, BETWEEN etc. INSERT-satsen använder data som returneras från en underfråga för att lägga in i en annan tabell.

En ORDER BY kan inte användas i en underfråga trots att huvudfrågan kan använda den. GROUP BY kan användas för att utföra samma funktion som ORDER BY i en underfråga. Underfrågor som returnerar fler än en rad kan bara användas med operatorer med fler värden som IN-operatorn. Operatorn BETWEEN kan inte användas med en underfråga, men däremot inuti underfrågan.

SELECT

Om vi förutsätter att medelåldern för en grupp människor är 43 år och vi vill visa alla som är äldre än snittet, skriver vi så här:

SELECT
    Namn,
    Age
FROM
    Personer
WHERE
    Age > 43;

I normala fall behöver vi dock räkna ut medelåldern och då kan vi använda oss av en underfråga i WHERE-satsen:

SELECT
    Namn,
    Age
FROM
    Personer
WHERE
    Age > (SELECT
               AVG(Age)
           FROM
               Personer);

I stället för att skriva 43 använder vi här en underfråga som returnerar medelåldern.

Man behöver inte använda aggregatfunktioner

select 
    Name, 
    Age
From 
    Personer
where 
    Age > (SELECT
        Age
    From
        Personer
    where
    Name= 'Suzanne');

Här visas alla personer som är äldre än Suzanne.

Temporära tabeller

Temporära används för lagring av temporär data, en slags mellanhand. De fungerar som vanliga tabeller och kan användas på samma sätt. För att skilja de från vanliga tabeller används prefixet # före namnet. Temporära tabeller kan endast kommas åt från den anslutningen som skapade den, men med globala temporära tabeller kan man komma åt dem från alla anslutningar.

Temporära tabeller tas bort automatiskt när anslutningen som skapade den stängs. Det gäller även för globala temporära tabeller, med undantag för om det vid tillfället som anslutningen stängs, utförs någon annan SQL-kod mot den temporära tabellen. Då raderas den efter att alla dessa transaktioner har slutförts.

Varje temporär tabell som du skapar får automatiskt ett unikt nummer i slutet på namnet, vilket gör att du inte behöver bekymra dig om att det finns någon annan med samma namn. Om du har fler än en tabell i samma batch eller lagrade procedur, måste de dock ha olika namn. När du refererar till den använder du dock bara den delen som du har namngett.

Främmande nycklar kan användas mellan de temporära tabellerna, men av förklarliga skäl, inte med referenser till de vanliga tabellerna.

Skapa en temporär tabell

Metod ett

Den här metoden går ut på att du väljer de poster som ska visas i en tabell, men i stället för att visa dem, lägger du in de i en temporär tabell med INTO och sedan ett namn för tabellen med prefixet #. På så sätt skapas också den temporära tabellen. För att visa dessa poster använder man sedan SELECT på den temporära tabellen.

SELECT
    ReceptNamn
INTO 
    #FavoritRecept
FROM
    Recept
WHERE
    ReceptNamn = 'Vårrullar'

SELECT * FROM #FavoritRecept

Metod två

Här skapar vi den temporära tabellen först och väljer namn för kolumner och datatyp. Sedan hämtar vi information med SELECT på samma sätt som den första metoden, utan INTO-satsen. I stället använder vi INSERT INTO och tabellnamnet efter prefixet #, innan vi skriver SELECT-satsen. Då kommer resultatet av SELECT-satsen läggas in i den nya temporära tabellen. Det måste vara lika många valda kolumner i SELECT-satsen som i den temporära tabellen.

CREATE TABLE #FavoritRecept(
    FavoritRecept nvarchar(100)
);

INSERT INTO 
    #FavoritRecept
SELECT
    ReceptNamn
FROM
    Recept
WHERE
    ReceptNamn = 'Vårrullar'

SELECT * FROM #FavoritRecept

Globala temporära tabeller

För att skapa en global temporär tabell använder man prefixet ##. I tempdb sparas namnet exakt som du skriver, utan någon unik identifierare. Detta eftersom den kan refereras från alla anslutningar. Det innebär att jag kan skapa en helt ny fråga och hämta resultatet från den här tabellen. Även om det här fungerar, finns det nog ingen stor anledning att använda en global temporär tabell, för har man många tabeller som behöver det här resultatet är det bättre att skapa en permanent tabell.

Temporära tabeller och lagrade procedurer

Det går att inkludera temporära tabeller i lagrade procedurer, även om de inte existerar i den anslutningen. Däremot går det inte att köra de i någon annan anslutning än där de skapades. Temporära tabeller som skapas i den lagrade proceduren, raderas när den lagrade proceduren har slutfört sin körning. Om en lagrad procedur skapar en temporär tabell och anropar en annan lagrad procedur, kommer den andre kunna använda den temporära tabellen som skapades i den första lagrade proceduren.

Radera temporära tabeller

Även om temporära tabeller raderas automatiskt, finns det situationer där det är en fördel att radera den manuellt. T ex om du har skrivit mycket kod och vill radera tabellen så snart du är färdig med den. Det ses också som en bra sed att de raderas när man är klar med dem.

Temporära tabeller

DROP TABLE #FavoritRecept

Globala temporära tabeller

DROP TABLE ##FavoritRecept

Tabellvariabler

Tabellvariabler liknar temporära tabeller, förutom att de är mer flexibla och de är alltid kvar i minnet. Om du har mindre än 100 rader är det lämpligt att använda tabellvariabler. Annars passar det bättre med temporära tabeller. Dessutom behöver de inte raderas när man är färdig med dem.

Skillnaden när man skapar en tabellvariabel är att man byter ut CREATE TABLE #FavoritRecept till DECLARE @FavoritRecept TABLE. Sedan använder man @ i stället när man hänvisar till den, som i INSERT INTO nedan.

DECLARE @FavoritRecept TABLE (
 FavoritRecept char(100)
 )
 INSERT INTO @FavoritRecept
 (FavoritRecept )
 SELECT
 ReceptNamn
 FROM
 Recept
 WHERE
 ReceptNamn= 'Vårrullar'

 

CTE – Common table expression

WITH common_table_expression

En CTE (Common Table Expression) specificerar ett tillfälligt namngivet delresultat och kan ses som en temporär tabell eller en lokal vy för just den här frågan. Flera CTE-uttryck kan anges genom att separera varje tabell med komma. En CTE kan inkludera referenser till sig själv. Med CTE lägger man in en tabelldefinition mellan parenteserna som i nedanstående exempel och sedan ställer man frågan.

WITH TempTabell AS (
    [...]
)

Ett riktigt exempel

Jag har lagt in en SELECT-sats i en CTE, som sammanfogar för- och efternamn samt namnger det med ett alias, ”Namn”.  Detta alias kan användas i SELECT-satsen efter CTE och då även i WHERE-satsen som annars inte kan använda sig av alias. I den vanliga SELECT-satsen, finns alias ”Namn” igen och den behöver vara med för att namnge kolumnen. Med den här metoden slipper vi upprepningar i WHERE-satsen.

WITH FirstAndLast AS (
    SELECT
        FirstName, LastName,
        FirstName + ' ' + LastName AS Namn
    FROM
        Persons
)

SELECT
    FirstName + ' ' + LastName AS Namn
FROM
    FirstAndLast
WHERE
    Namn LIKE '%u%';

Datum, tid och funktioner

Att hantera datum och tid kan vara lite knepigt i början, men om du använder formatet 1950-03-22 fungerar det som det ska. Om du däremot använder 22/03/1950 blir det fel. Det beror på att den antar att det är den tredje dagen i månad tjugotvå som inte finns. Alltså USA-format.

Ett tvåsiffrigt årtal som är mindre än eller lika med 49, blir 2000-talet och om det är större än 49 blir 1900-talet. Alltså, 38 och 49 blir 2038 och 2049, medan 50 och 90 blir 1950 och 1990. Det bästa är dock att använda fyra siffror för årtal där det är möjligt.

Standardformat för datum bestäms av nuvarande språkinställningen. Du kan ändra datumformat genom att använda satserna SET LANGUAGE som beskrivs längre ner och SET DATEFORMAT. Det första du antagligen vill göra med ett datum är att omvandla det till ett mer läsbart format. För det använder man funktionen CONVERT som beskrivs längre ner.

Från minuter till timmar

Timmar

Om man har tabellen filmer och i den har en kolumn med längden i minuter, behöver man dela antalet minuter med 60, för att få fram antalet timmar.

SELECT
    FilmName,
    FilmMinutes / 60 AS Timmar
FROM
    Film;

Resultatet i kolumnen ”Timmar” för ett film på 132 minuter blir 2.

Ovanstående exempel visar först kolumnen med namnet på filmerna och en kolumn med det totala antalet minuter för varje film. Sedan skapas även kolumnen med alias ”Timmar” som visar antalet timmar. Eftersom jag använder mig av en heltalsdivision (båda datatyperna i uttrycket är av typen INT), blir resultatet enbart timmar. Om jag i stället skulle delat med ett decimaltal (60.0), hade svaret blivit det också.

SELECT
    FilmName,
    FilmMinutes / 60.0 AS Timmar,
FROM
    Film;

Resultatet i kolumnen ”Timmar” för en film på 132 minuter blir. 2.20.

Eftersom det är timmar vi är ute efter i den nya kolumnen, är det givetvis en heltalsdivision som vi ska använda oss av. Däremot behöver vi göra ytterligare kalkyler för att visa de minuterna som är kvar. Två timmar är 120 minuter, vilket innebär att en totallängd på 132 minuter får 12 minuter kvar.

Minuter

Man skulle därför kunna dela med 60.0 för nästa kolumn med alias ”Minuter”. Då blir resultatet 2.20 som i föregående exempel, där siffran 2 till vänster om punkten är timmar. Dessa timmar tar man sedan bort genom att subtrahera med heltalsdivisionen. Kvar blir då 0.2. För att omvandla detta decimaltal till minuter måste man sedan multiplicera med 60.

SELECT
    FilmName,
    FilmMinutes / 60 AS Timmar,
    (FilmMinutes / 60.0 - FilmMinutes / 60) * 60 AS Minuter
FROM
    Film;

Även om  det här fungerar är det en onödig väg att gå och det kan också bli en minut för lite om vi inte utför ytterligare operationer.

Timmar och minuter

Lösningen är att använda den aritmetiska operatorn modulus (%). Med den gör man en heltalsdivision och svaret blir den rest som är kvar. Så för 132 minuter blir det 12 minuter kvar efter att två timmar, alltså 120 minuter har dragits ifrån.

SELECT
    FilmName,
    FilmMinutes / 60 AS Timmar,
    FilmMinutes % 60 AS Minuter
FROM
    Film;

Funktioner för datum

Convert

CONVERT(data_type(length) , expression, style)

Funktionen Convert, som egentligen tillhör konverteringsfunktioner, används för att konvertera ett uttryck från en datatyp till en annan och den är specifik för SQL Server med utökade funktioner för att formatera datum. Första argumentet är datatypen och längden på den. Andra argumentet är uttrycket och tredje argumentet är ett heltal som specificerar hur Convert-funktionen ska översätta uttrycket.

Convert(Char(8), FilmReleaseDate, 3)

Resultatet blir 16/07/93, som är brittiskt standardformat.

Siffran 3 innebär brittiskt standardformat. För att få brittiskt standardformat med fyra siffror för året används siffran 103. Eftersom det innebär ytterligare två tecken måste Char ändras från 8 till 10.

Datename

Datename(datepart, date)

Returnerar en sträng som representerar den specificerade delen av det angivna datumet. Första argumentet är den delen av datumet som ska returneras t ex year, month, week, hour eller minute. Användardefinierade variabler är inte giltiga. Andra argumentet är ett uttryck som kan omvandlas till tid eller datum. Det kan vara ett uttryck, kolumnuttryck, användardefinierad variabel eller en sträng. För att undvika oklarheter, använd fyrsiffriga år.

SELECT
    Name,
    BirthDate,
    Datename(M, BirthDate) AS 'Månad'
FROM
    Personer;

Här skapas en ny kolumn med alias ”Månad” där personernas födelsemånad skrivs ut, t ex June.

Med Datename i stället för Convert, kan man bygga upp ett format som inte är standard.

SELECT
    Name,
    BirthDate,
    Convert(Char(10), BirthDate, 103),
    Datename(WEEKDAY,BirthDate) + ', ' +
    Datename(DAY,BirthDate) + ' ' +
    Datename(MONTH,BirthDate) + ' ' +
    Datename(YEAR,BirthDate)
FROM
    Personer;

Result för 19930510 blir ”Friday, 10 may 1993”.

Svenska namn för månader och veckodagar

När man väljer veckodag och månad i textformat returneras det engelska ordet. Hur får man dessa på svenska? Finns det någon anledning att göra detta i databasen eller är det alltid bäst att låta klientsidan sköta det här? 

I nuläget vet jag inte vilka metoder som är bäst, men ett sätt att lösa det på i databasen är att använda CASE-satsen. För månader blir det då en CASE-sats med tolv par av WHEN och THEN och för veckodagar blir det en CASE-sats med sju par av WHEN och THEN.

SELECT
    Name,
    BirthDate,
    Datename(WEEKDAY,BirthDate),
    CASE
        WHEN Datename(WEEKDAY,BirthDate) = 'Monday' THEN 'Måndag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Tuesday' THEN 'Tisdag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Wednesday' THEN 'Onsdag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Thursday' THEN 'Torsdag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Friday' THEN 'Fredag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Saturday' THEN 'Lördag'
        WHEN Datename(WEEKDAY,BirthDate) = 'Sunday' THEN 'Söndag'
    END AS Dagar
FROM
    Personer;

Även om det fungerar känns det inte som en bra metod och ännu värre med månader som skulle kräva tolv par av WHEN och THEN. En bättre lösning kanske är att skapa ytterligare en tabell som fungerar som en liten ordlista, en kolumn med det svenska namnet för veckodagar och en kolumn för det engelska namnet?

SELECT
    Name,
    BirthDate,
    Swedish as Veckodagar
FROM
    Personer
    INNER JOIN SwedishWeekdays
    ON SwedishWeekdays.English = Datename(WEEKDAY,BirthDate);

Här kontrolleras om veckodagen som funktionen Datename returnerar matchar någon av de sju raderna i kolumnen ”English” i tabellen ”SwedishWeekdays” och när de matchar returneras värdet i kolumnen ”Swedish” på samma rad.

Jag vet inte om det finns någon anledning att använda sig av det senare exemplet heller, för det finns ett betydligt enklare sätt. Man ändrar helt enkelt standardspråket från engelska till svenska.

SET LANGUAGE swedish;

Här ändras standardspråket till svenska för den pågående sessionen. För att återgå till engelska inom samma session ändrar du ”swedish” till ”us_english” och kör koden en gång till. Det här var ju enkelt. Varför visade jag inte det här med en gång? Jo, för man lär sig mer och kommer framförallt ihåg saker och ting lättare genom att testa sig fram.

Datepart

Datepart ( datepart , date )

Returnerar en integer som representerar den specificerade delen av det angivna datumet. Värdet som returneras beror på språkmiljön som är satt genom att använda SET LANGUAGE och genom att konfigurera standardspråket för servern vid inloggning. Det senare tänker jag inte beskriva närmare just nu, men mer information finns på den här engelska sidan.

Första argumentet är den delen av datumet som en motsvarande integer ska returneras för t ex year, month, week, hour eller minute. Användardefinierade variabler är inte giltiga. Andra argumentet är ett uttryck som kan omvandlas till tid eller datum. Det kan vara ett uttryck, kolumnuttryck, användardefinierad variabel eller en sträng.

Datepart(weekday, Getdate()) AS 'Veckodag'

Här returneras siffran 3 om det är tisdag, eftersom söndag är den första dagen i veckan som standard.

För att ändra standardinställningen för första dagen i veckan till svenska, kan man använda SET LANGUAGE eller SET Datefirst.

Datediff

Datediff ( datepart , startdate , enddate )

Returnerar tiden mellan två datum. I exemplet nedan visas en ny kolumn med alias ”Period” och antalet år mellan BirthDate och 2016-03-17.

Obs. Resultatet blir inte exakt! Se DATEADD nedan.

SELECT
    Name,
    BirthDate,
    Datediff(YEAR, BirthDate, '2016-03-17') AS 'Period'
FROM
    Personer;

Datediff och Getdate

Man behöver inte bara använda en funktion per kalkyl, utan det går bra att kapsla in flera funktioner. I exemplet med Datediff ovan skrev jag in dagens datum 2016-03-17 (i skrivandets stund). Om det alltid är den aktuella dagens datum som jag vill använda, är det bättre att använda en funktion som visar aktuellt datum. Så i stället för ovanstående exempel, ska jag använda funktionen Getdate som en parameter till funktionen Datediff. Getdate visar aktuellt datum och aktuell tid och den tar inga argument.

SELECT
    Name,
    BirthDate,
    Datediff(YEAR, BirthDate, Getdate()) AS 'Period'
FROM
    Personer;

DATEADD

Dateadd (datepart , number , date )

Funktionen DATEADD används för att lägga till (eller dra ifrån) ett specificerat tidsintervall till ett datum. Första argumentet är den delen av datumet (t ex år eller månad) som man lägger till ett heltalsnummer. Andra argumentet är heltalet (positivt eller negativt) som läggs till. Om man anger ett värde med en decimal, avrundas det inte utan slängs bort. Det tredje argumentet är någon form av datum eller tid. Det kan vara ett uttryck, kolumnuttryck, användardefinierad variabel eller en sträng. Datatypen som returneras är samma typ som date-argumentet, förutom för strängar som i stället omvandlas till datetime.

Resultatet med Datediff ovan blir inte exakt, eftersom beräkningen görs för år. För att göra en exakt beräkning blir det mer komplicerat. Man börjar med att räkna ut födelsedagen för personen under nuvarande år. I det första argumentet specificerar man den delen av datumet som man ska lägga till ett heltal. Det andra argumentet är antalet påbörjade år och dessa år läggs på datumet i det tredje argumentet. Som andra argument har jag alltså lagt in exakt samma funktion som i föregående exempel med Datediff och Getdate, eftersom den returnerar antal påbörjade år.

Det tredje argumentet är det exakta datumet när personen föddes. Eftersom vi lägger till antalet påbörjade år (eftersom vi har angett år i första argumentet), blir resultatet nuvarande år, men månad och dag förblir det samma som datumet personen föddes. Nu har vi alltså födelsedagen för nuvarande år.

Dateadd(YEAR, Datediff(YEAR, BirthDate, Getdate()), BirthDate)

Nästa steg är att kontrollera om födelsedagen har passerat det här året eller inte. Det kan göras genom att lägga in det i en CASE-sats. I nedanstående kod kontrollerar jag först om nuvarande datum är mindre än födelsedatumet i år. Om det är det, subtraherar jag koden för antalet år med 1. Annars visas antalet år utan förändring.

SELECT
    Name,
    BirthDate,
    Datediff(YEAR, BirthDate, Getdate()) AS 'Period',
    Dateadd(YEAR, Datediff(YEAR, BirthDate, Getdate()), FilmReleaseDate),
    CASE
        WHEN Getdate() &amp;lt; Dateadd(YEAR, Datediff(YEAR, BirthDate, Getdate()), BirthDate)
        THEN Datediff(YEAR, BirthDate, Getdate())-1
        ELSE Datediff(YEAR, BirthDate, Getdate())
    END
FROM
    Personer;

@@Datefirst

Returnerar det nuvarande värdet för en session av SET Datefirst. SET Datefirst specificerar första dagen i veckan. Alltså 1 till 7, där måndag är 1. Standard för engelska (USA) är 7, eftersom söndagar räknas som första dagen i veckan där.

SET LANGUAGE us_english;
SELECT @@Datefirst;

Här returneras 7 och eftersom engelska (USA) redan är standard, hade jag egentligen inte behövt använda SET LANGUAGE. Om jag i stället hade valt ”Swedish” skulle resultatet blir 1.

Det går också att använda SET DATEFIRST för att ange vilken dag som är den första dagen i veckan.

SET Datefirst 1;

Här returneras 1 som betyder att måndag är den första dagen i veckan.

Year, Month och Day

Day

Day ( date )

Returnerar ett heltal som representerar dagen i månaden av det specificerade datumet.

Month

Month ( date )

Returnerar ett heltal som representerar månaden av det specificerade datumet.

Year

Year ( date )

Returnerar ett heltal som representerar året av det specificerade datumet.

De här funktionerna är användbara om man t ex vill visa personer som är födda ett visst år, en viss månad eller en viss dag.

SELECT
    BirthDate
FROM
    Personer
WHERE&nbsp;
    Year(birthdate) = '1968' AND Month(birthdate) = '01' And Day(BirthDate) = '09';

Här visas enbart personer som är födda 1968-01-09.

Datum med < och >

Om du vill visa alla resultat före eller efter ett visst datum kan du använda operatorerna mindre än eller större än. För att nedanstående ska fungera måste datatypen för datumet vara av rätt typ som Date och String. Datetime fungerar inte eftersom den inkluderar även tid.

WHERE BirthDate &gt; '1950-03-22'

Här visas alla födelsedagar efter 1950-03-22.

Jämföra flera resultat med AND och OR

De med efternamn som börjar på ”Pa” och födelseår ”1947”.

WHERE
 LastName LIKE 'Pa%' AND Year(BirthDate) = '1947'

Datepart argument

Här nedan listas alla giltiga argument för datepart.

datepart Förkortningar
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz
ISO_WEEK ISOWK, ISOWW

Varje datepart och deras förkortningar returnerar samma värde.

Fler funktioner för datum och tid

CURRENT_TIMESTAMP, DATEDIFF_BIG (2016), DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH, GETUTCDATE, ISDATE, SMALLDATETIMEFROMPARTS, SWITCHOFFSET, SYSDATETIME, SYSDATETIMEOFFSET, TIMEFROMPARTS, TODATETIMEOFFSET, DAY, MONTH, YEAR

Lagrade prodedurer

En lagrad procedur är en eller flera SQL-satser, grupperade som en enhet och med ett nytt namn. Sedan kan man köra den genom att enbart ange EXECUTE och det tilldelade namnet, i stället för att skriva hela koden med alla SQL-satser. Fördelen med detta är att det blir mindre kod att skriva.

Skapa en lagrad procedur

Om vi tar nedanstående fråga som exempel och tänker oss att vi vill köra den här frågan flera gånger vid olika tillfällen. Då vill man helst undvika att skriva koden om och om igen. Det är där lagrade procedurer kommer in.

SELECT
    ReceptNamn,
    Beskrivning
FROM
    Recept
ORDER BY
    ReceptNamn Asc;

För att skapa en lagrad procedur, börjar man med att ovanför satserna som ska ingå i den skriva CREATE PROCEDURE (Proc) och ett valfritt namn för den. Sen skriver man order AS som kommer konvertera den efterföljande satserna till en lagrad procedur. Det kan också vara bra att se till att alla satser som tillhör den lagrade proceduren ligger innanför ett block med BEGIN och END. För en enkel SELECT-sats som nedan spelar det inte så stor roll, men å andra sidan gör det ingen skada heller och det är bra att lägga sig till med goda vanor.

För att inte riskera att göra ändringar i fel databas, är det också bra att börja skriptet med USE och namnet på den databas man vill använda. Eftersom CREATE PROCEDURE måste vara den första satsen i en batch, blir det då problem. Lösningen är att använda GO som betyder att en ny batch börjar där.

USE Recept;
GO
CREATE PROCEDURE ReceptProc
AS
BEGIN
    SELECT
        ReceptNamn,
        Beskrivning
    FROM
        Recept
    ORDER BY
        ReceptNamn Asc;
END

Här skapas den lagrade proceduren ”ReceptProc”.

Köra en lagrad procedur

Nu är det bara att använda kommandot EXECUTE tillsammans med namnet på proceduren för att köra den. Resultatet blir detsamma som när man skriver alla satserna som i första exemplet.

EXECUTE ReceptProc;

Ändra en lagrad procedur

För att ändra en lagrad procedur använder man samma metod som när man skapar en, förutom att man byter ute CREATE mot ALTER. Sedan gör man de ändringar man vill göra.

Om man precis har skapat proceduren är det bara att ändra den koden. Annars kan man i SQL Server Management Studio högerklicka på den lagrade proceduren och välja ”Modify”. Den ligger i ”Programmability/Stored Procedures” i den databasen som man lagt den i.

USE Recept;
GO
ALTER PROCEDURE ReceptProc
AS
BEGIN
    SELECT
        ReceptNamn,
        Beskrivning
    FROM
        Recept
    ORDER BY
        ReceptNamn Desc;
END

Här har jag ändrat ordningen från ASC till DESC.

Ta bort en lagrad procedur

I SQL Server Management Studio kan man högerklicka på namnet på den lagrade proceduren och välja att ta bort den. Koden för att ta bort den är:

DROP PROCEDURE ReceptProc;

Parametrar

Lägga till parametrar

Parametrar placeras alltid innanför två rundparenteser direkt efter namnet på den lagrade proceduren. Parametern (variabeln) måste börja med ett snabel-A som i @income i exemplet nedan. Sen måste man göra något med parametern också och det gör jag i en WHERE-sats. Jag kontrollerar helt enkelt vilka inkomster som är större en variabeln @income när jag har skapat den här lagrade proceduren och kör den.

USE MyDatabase;
GO
CREATE PROCEDURE IncomeProc(@income AS INT)
AS
BEGIN
    SELECT
        Name,
        Income
    FROM
        Personer
    WHERE
        Income > @income
    ORDER BY
        FirstName Asc;
END

Köra en lagrad procedur med parametrar

Att köra en lagrad procedur med parametrar fungerar på samma sätt som utan, men man måste också ha med en parameter.

EXECUTE IncomeProc 10000;

Här har jag gett parametern värdet 10000 och när koden kör, kommer WHERE-satsen att kontrollera vilka inkomster som är större än 10000 och visa de posterna.

Fler parametrar

Det går att använda fler parametrar med ett kommatecken emellan dem. Det bästa är att placera varje parameter på en egen rad och även parenteserna på egna rader.

I WHERE-satsen lägger jag sedan till ännu en kontroll för den andra parametern och eftersom båda villkoren ska uppfyllas använder jag AND mellan dem.

USE MyDatabase;
GO
CREATE PROCEDURE IncomeProc
    (
        @MinIncome AS INT,
        @MaxIncome AS INT
    )
AS
BEGIN
    SELECT
        FirstName,
        Income
    FROM
        Persons
    WHERE
        Income >= @MinIncome AND
        Income <= @MaxIncome
    ORDER BY
        FirstName Asc;
END

Här kontrolleras om inkomsterna är större än eller lika med @MinIncome och mindre än eller lika med @MaxIncome.

Köra en lagrad procedur med flera parametrar

Här är inga konstigheter heller. Man separerar parametrarna med kommatecken.

EXECUTE IncomeProc 10000, 20000;

Namnge parametrar

Man kan namnge parametrar när man kör den lagrade proceduren.

EXECUTE IncomeProc @MinIncome = 10000, @MaxIncome = 20000;

Textparameter

Att skapa en parameter (variabel) för text fungerar på samma sätt som för INT. Man måste bara använda en datatyp för just text, som jag har gjort här nedan med nvarchar(100). I WHERE-satsen hade jag tänkt att kontrollera om förnamnet innehåller en viss sträng. För strängen ”lott” skriver man då LIKE ‘%lott%’. Men det kan vi inte göra här eftersom vi inte vet vilken sträng vi vill söka efter. I stället har vi variabeln @Title. Det går heller inte att skriva ‘@Title%’ eftersom allt innanför de enkla citationstecknen tolkas som text. Man måste i stället sätta ihop text med variabeln på ett korrekt sätt och det görs med plus-tecknet som i nedanstående exempel.

USE MyDatabase;
GO
ALTER PROCEDURE IncomeProc
    (
         @MinIncome AS INT,
         @MaxIncome AS INT,
         @Title AS nvarchar(100)
    )
AS
BEGIN
    SELECT
        FirstName,
        Income
    FROM
        Persons
    WHERE
        Income >= @MinIncome AND
        Income <= @MaxIncome AND
        FirstName LIKE '%' + @Title + '%'
    ORDER BY
        FirstName Asc;
END

Valfria parametrar

Parametrarna som vi hittills har använt, måste anges när man kör den lagrade proceduren, men man kan också skapa valfria parametrar. För att göra det, tilldelar man ett värde för varje parameter med tecknet lika med.

USE MyDatabase;
GO
ALTER PROCEDURE IncomeProc
    (
         @MinIncome AS INT = 10000,
         @MaxIncome AS INT = 20000,
         @Title AS nvarchar(100) = 'a'
    )
AS
BEGIN
    SELECT
        FirstName,
        Income
    FROM
        Persons
    WHERE
        Income >= @MinIncome AND
        Income <= @MaxIncome AND
        FirstName LIKE '%' + @Title + '%'
    ORDER BY
        FirstName Asc;
END

Här är det inte längre nödvändigt att ange parametern @Title och när man utesluter den får den standardvärdet a.

Case

CASE-satsen utvärderar en lista av villkor och det åtföljs av minst ett par WHEN och THEN satser. Den måste avslutas med END, men ELSE är valfritt. ELSE fångar upp värden som inte är specificerade. CASE kan användas i vilket uttryck eller vilken sats som helst som tillåter ett giltigt uttryck. Till exempel, kan du använda CASE i uttryck som SELECT, UPDATE, DELETE och SET, samt i satser som select_list, IN, WHERE, ORDER BY och HAVING. CASE-satsen har två format:

Den enkla CASE-satsen

Den enkla CASE-satsen jämför ett uttryck med statiska värden. Endast i den enkla CASE-satsen, skriver man ”column_name” efter CASE. Värdena ”value1” och ”value2” skiljer sig mot den sökta CASE-satsen, som i stället har villkor där.

CASE column_name
WHEN ”value1” THEN ”result1”
WHEN ”value2” THEN ”result2”

END

 

Exempel:

Här används CASE för att lägga till 500 kronor i budgeten för trämaterial och 1000 kronor i budget för verktyg. ”Ny budget” är den nya kolumnen för CASE-satsen.

SELECT
    ProjektNamn,
    Budget,
CASE ProjektNamn
    WHEN 'WorkingTools' THEN Budget + 1000
    WHEN 'WorkingWood' THEN Budget + 500
ELSE Budget
END
    "Ny budget"
FROM
    HomeProject;

Den sökta CASE-satsen

Den sökta CASE-satsen jämför ett uttryck med en eller flera logiska villkor. Här har ”column_name” i den enkla CASE-satsen, flyttat ner till ”value1” och ”value2” och i stället bildat ”condition1” och ”condition2”.

CASE
WHEN ”condition1” THEN ”result1”
WHEN ”condition2” THEN ”result2”

END

 

Exempel:

SELECT
    FilmName,
    FilmMinutes,
CASE
    WHEN FilmMinutes <= 90 THEN 'Short'
    WHEN FilmMinutes <= 150 THEN 'Medium'
    WHEN FilmMinutes <= 180 THEN 'Long'
ELSE 'Mer än 180 minuter'
END AS [Kolumnalias]
FROM
    Film;

Observera att i ovanstående exempel måste man börja med 90 och sluta med 180, annars får alla filmer som är 180 minuter eller kortare, värdet Long. För att undvika överlappning, kan man lägga till uttrycket FilmMinutes > 150 efter 180 med AND emellan och på samma sätt efter 150, FilmMinutes > 90. Hela CASE-satsen är en vald kolumn precis som FilmName och FilmMinutes och där ska det vara ett kommatecken efter FilmMinutes. Om jag väljer ytterligare en kolumn efter CASE-satsen, ska det också avslutas med ett kommatecken.

CASE-sats och WHERE

Det går även bra att lägga till en WHERE-sats och eftersom den satsen inte fungerar med alias, måste vi lägga in hela kolumnen. Alltså hela CASE-satsen utan alias.

SELECT
    FilmName,
    FilmMinutes,

CASE
    WHEN FilmMinutes <= 90 THEN 'Short'
    WHEN FilmMinutes <= 150 THEN 'Medium'
    WHEN FilmMinutes <= 180 THEN 'Long'
ELSE 'Mer än 180 minuter'
END AS [Kolumnalias]
FROM
    Film
WHERE
CASE
    WHEN FilmMinutes <= 90 THEN 'Short'
    WHEN FilmMinutes <= 150 THEN 'Medium'
    WHEN FilmMinutes <= 180 THEN 'Long'
ELSE 'Mer än 180 minuter'
END = 'Medium';

Här visas alla filmer som är har längden medium. Att upprepa hela CASE-satsen kanske inte är så smidigt. Kanske är det en bättre idé att använda CTE?

CASE – Söka ord i titel

Ett annat sätt att använda CASE på är att kontrollera om ett ord eller en fras förekommer i filmtiteln. För det använder man LIKE och wildcard.

SELECT
    FilmName,

CASE
    WHEN FilmName LIKE '%star trek%' THEN 'Inte bra'
ELSE 'Vill se'
END
FROM
    Film;

CASE – Datum, tidsperioder

CASE är också användbart för att kontrollera datum och tidsperioder.

SELECT
    FilmName,
    FilmDate,

CASE
    WHEN FilmDate < '1980-01-01' THEN 'Gamla filmer'
ELSE 'Moderna filmer'
END
FROM
    Film;