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.

Kommentera

E-postadressen publiceras inte. Obligatoriska fält är märkta *