Stored Procedure er programstumper der gemmes i databasen

Stored Procedure er programstumper, der gemmes i databasen og afvikles op databaseserveren på samme måde som forespørgsler. Med Stored Procedures kan man på den måde flytte basal behandlig op på serveren og opnå en både mere optimal og sikker afvikling. Fra C# programmer kan man aktivere Stored Proceures med Command objektets meroder: Execute. Non. Query: Hvis der ikke ønskes brugt noget resultat fra den afviklede Stored Procedure. Execute. Scalar: Hvis der ønskes et enkelt resulat fra den afviklede Stored Procedure Execute. Reader: Hvis en Stored Procedure aflevere en forespørgsel som resultat og man ønsker at anvende denne. For at afvilke en Stored Procedure anvendes SQL udtrykket EXEC efterfulgt af parametrene, enten i som værdier i den declarerede rækkefølge med komma imellem eller ved at angive parameterens navn = tildelt værdi. Dette vises i de efterfølgende eksempler

Eksempler på Stored Procedure Primitiv beregnings procedure, der illustrere simple parametre og returværdier Der kan returneres én værdi. CREATE PROCEDURE Broek. Vaerdi ( @taeller int = 1, @naevner int = 1 ) AS BEGIN DECLARE @result int IF @naevner = 0 BEGIN RETURN 0. 0 END ELSE BEGIN set @result = @taeller / @naevner; RETURN @result END

Eksempler på Stored Proceduren kaldes enten med parametrene i rækkefølge, som de er erklæret i proceduren, eller med navngivning. Resultater kan bruges lokalt i declarede variable. Hvis værdier skal bruges uden for SQL, kan de returneres i en forespørgsel. Denne forespørgsel læses fra C# med Data. Reader. DECLARE @tal 1 int; DECLARE @tal 2 int; EXEC @tal 1 = Broek. Vaerdi 10, 2 EXEC @tal 2 = Broek. Vaerdi @naevner=2, @taeller=10 SELECT @tal 1, @tal 2 /* resultat i en forespørgsel*/

Eksempler på Stored Procedure CREATE PROCEDURE Find. Person. Med. Efternavn ( @efternavn varchar(10) = '%' ) AS BEGIN select * from person where efternavn like @efternavn; END Eksempler på brug af proceduren a) EXEC Find. Person. Med. Efternavn “Larsen%” b) EXEC Find. Person. Med. Efternavn

Eksempler på Stored Procedure CREATE PROCEDURE Opret. Afdeling /* med muligt autonr*/ ( @afdnr int, @afdnavn varchar(30) ) AS BEGIN insert into afdeling (Afdnr, Afd. Navn) Values (@afdnr, @afdnavn); END Eksempler på brug af proceduren a) EXEC Opret. Afdeling 10, ‘Afd ti’ b) EXEC Opret. Afdeling @afdnr=10 @afdnavn=‘Afd ti’ c) EXEC Opret. Afdeling @afdnavn=‘Afd ti’ @afdnr=10

Eksempler på Stored Procedure CREATE PROCEDURE Opret. Afdeling 2 /* med muligt autonr*/ ( @afdnr int = -1, @afdnavn varchar(30) ) AS BEGIN TRANSACTION IF @afdnr < 0 BEGIN select @afdnr = max(afdnr) from afdeling; set @afdnr = @afdnr +1; END insert into afdeling (Afdnr, Afd. Navn) Values (@afdnr, @afdnavn); select * from afdeling WHERE Afd. Nr=@afdnr; COMMIT TRASACTION END Eksempler på brug af proceduren a) EXEC Opret. Afdeling 2 @afdnr=10 @afdnavn=‘Afd ti’ b) EXEC Opret. Afdeling 2 @afdnavn=‘Afd auto’

Eksempler på Stored Procedure CREATE PROCEDURE Opret. Peson /* I tabel hvor key er def. som identity autonummerering*/ ( @navn varchar(30) ) AS BEGIN insert into Person (Navn) Values (@navn); select * from Person WHERE Id=@@identity; END Eksempler på brug af proceduren a) EXEC Opret. Person ‘Bjørk’ b) EXEC Opret. Person @navn=‘Busch’

Eksempler på Stored Procedure CREATE PROCEDURE Opret. Konto. Postering ( @Konto. ID int, @Beloeb int, @Tekst varchar(30) ) AS BEGIN DECLARE @Saldo int BEGIN TRANSACTION INSERT INTO Transakion (konto. ID, Beloeb, Tekst) Values (@Konto. ID@Beloeb, @Tekst); UPDATE Konto SET Saldo=Saldo+@Beloeb WHERE Konto. ID=@Konto. ID; SELECT @Saldo = Saldo FROM konto WHERE Konto. ID=@Konto. ID; IF @Saldo < 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION END Eksempel på brug af proceduren EXEC Opret. Konto. Postering @Konto. ID=1002 @Beloeb=100 @Tekst=‘kontantudbetaling‘

Eksempler på Stored Procedure CREATE PROCEDURE Ret. Person. Navn 2 ( @Person. Nr int, @Fornavn varchar(30), @Efternavn varchar(30), @Old. Fornavn varchar(30), @Old. Efternavn varchar(30) ) AS BEGIN DECLARE @XFornavn varchar (30) DECLARE @XEfternavn varchar (30) DECLARE @outtable as table /* temporær tabel til rettede poster – før ændring */ ( Fornavn varchar(30) , Efternavn varchar(30) ) BEGIN TRANSACTION UPDATE Person SET Fornavn=@Fornavn , Efternavn=@Efternavn OUTPUT deleted. Fornavn, deleted. Efternavn INTO @outtable /* her fås gamle poster*/ WHERE Person. Nr=@Person. Nr; SELECT @XFornavn = Fornavn, @XEfternavn= Efternavn FROM @outtable; IF @XFornavn = @Old. Fornavn AND @XEfternavn = @Old. Efternavn BEGIN COMMIT TRANSACTION RETURN 1 END ELSE BEGIN print 'Roolback da data var ændret før opdatering' ROLLBACK TRANSACTION RETURN 0 END Eksempel på brug af proceduren EXEC Ret. Person. Fornavn 5, 'Ny. Fornavn', ‘Ny. Efternavn‘, ‘Gl. Fornavn’, ‘Gl. Efternavn Denne løsning sikre at præcis de felter vi ændrer ikke er rettet af andre undervejs. Dette er en “stærkere” løsning end “bare” at sikre at rækken ikke er ændret.