Dzisiaj zajmiemy się tematem procedury składowane, w literaturze niekiedy nazywane osadzonymi. Najprostsza definicja jaka przychodzi mi do głowy to taka, że procedury składowane to zbiór instrukcji SQL, które zapisujemy pod jedną nazwą. Następnie możemy odwołać się do danej procedury (wywołać ją) za pomocą tej nazwy wraz z parametrami wejściowymi które wcześniej definiujemy w procedurze. Struktura procedury składowanej (najprostsza jaką znalazłem) poniżej.

Definicja procedury składowanej (najprostszej jaką znalazłem).

CREATE PROCEDURE nazwa_procedury
	(
	  	@parametr_1 typ_naszego_parametru_1,
		 …
		@parametr_n typ_naszego_parametru_n,
	)
AS
	treść_zapytania

ale np. przy próbie utworzenia nowej procedury składowanej w SQL Server otrzymamy automatycznie kod (bez kometarzy).

CREATE PROCEDURE  
	<@Param1, sysname, @p1>  = , 
	<@Param2, sysname, @p2>  = 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

Przykład

Utwórz procedurę która będzie pobierała dwa parametry Imię , Nazwisko i w zależności od tych parametrów będzie podawała dane teleadresowa danej osoby: Adres, E-mail, Telefon, Typ Telefonu.

CREATE PROCEDURE ProcOsobyInformacje
	(
		 @imie varchar(10)
		,@nazwisko varchar(20)
	)
AS
BEGIN
	SELECT
	 osoba.BusinessEntityID AS [ID osoby]
	,osoba.FirstName AS [Imie]
	,CASE
		WHEN osoba.MiddleName IS NULL THEN 'b.d.'
		ELSE osoba.MiddleName
	 END AS [Drugie Imie]
	,osoba.LastName AS Nazwisko
	,CASE
		WHEN adres.City IS NULL THEN 'b.d.'
		ELSE adres.City
	 END AS Miasto
	 ,CASE
		WHEN adres.AddressLine1 IS NULL THEN 'b.d.'
		ELSE adres.AddressLine1
	 END AS Adres
	 ,CASE
		WHEN email.EmailAddress IS NULL THEN 'b.d.'
		ELSE email.EmailAddress
	 END AS [E-mail]
	 ,CASE
		WHEN telefon.PhoneNumber IS NULL THEN 'b.d.'
		ELSE telefon.PhoneNumber
	 END AS Telefon
	 ,CASE
		WHEN telefontyp.Name IS NULL THEN 'b.d.'
		ELSE telefontyp.Name
	 END AS [Typ Telefonu]
	
	
FROM
	Person.Person osoba
	LEFT JOIN Person.BusinessEntityAddress pwoa ON osoba.BusinessEntityID = pwoa.BusinessEntityID
	LEFT JOIN Person.Address adres ON pwoa.AddressID = adres.AddressID
	LEFT JOIN Person.EmailAddress email ON osoba.BusinessEntityID = email.BusinessEntityID
	LEFT JOIN Person.PersonPhone telefon ON osoba.BusinessEntityID = telefon.BusinessEntityID
	LEFT JOIN Person.PhoneNumberType telefontyp ON telefon.PhoneNumberTypeID = telefontyp.PhoneNumberTypeID

WHERE
	osoba.FirstName = @imie 
	AND osoba.LastName = @nazwisko	
END

Uruchamiamy nasz kod F5. I jeżeli wszystko jest w porządku to otrzymamy komunikat „Command(s) completed successfully.”. W tym momencie w gałęzi Databases -> AdventureWorks2008R2 -> Programmability -> Stored Procedures powinna pojawić się procedura o nazwie ProcOsobyInformacje.

Teraz możemy przetestować naszą procedurę, czy rzeczywiście zwraca poprawne wyniki. Wyszukamy informacje dotyczące osoby(lub osób) Terri Duffy. W tym celu klikamy prawym przyciskiem myszy (dalej PPM) i wybieramy opcję Execute Stored Procedure….

W oknie Execute Procedure w tabelce ze zmiennymi (parametrami) w kolumnie Value wpisujemy odpowiednie wartości, dla imienia Terri, a dla nazwiska Duffy i nasz wybór potwierdzamy przyciskiem OK.

Poniżej efekt wykonania naszej procedury.

Widzimy, że SQL Server znalazł dwie osoby o takim samym imieniu i nazwisku, różniące się drugim imieniem. Teraz możemy sprawdzić czy jest to prawda w tabeli Person.Person zapytaniem wyszukującym osoby o tym imieniu i nazwisku.

SELECT 
       PersonType
      ,FirstName
      ,MiddleName
      ,LastName
  FROM Person.Person
  where LastName = 'Duffy'

Wynik uruchomienia naszego zapytania.

Widzimy, że wszystko się zgadza.

Wywołać procedurę składowaną możemy także „ręcznie” wpisując w okno zapytania następujący kod.

EXEC ProcOsobyInformacje 'Terri','Duffy'

Efekt będzie identyczny

SQL Server posiada także procedury już wbudowane oto najbardziej użyteczne z nich: sys.sp_addrolemember, sys.sp_adduser, sys.sp_catalogs, sys.sp_columns, sys.sp_databases itd. ale o tym w kolejnych wpisach.

Zapraszam do lektury kolejnych wpisów na Anonco.Pl

Pamiętaj:

• Nie możesz utworzyć dwóch obiektów w bazie o takiej samej nazwie np. widok i procedura składowa. Jeżeli zdarzy się taka sytuacja w momencie próby utworzenia drugiego obiektu o tej samej nazwie co pierwszy otrzymasz komunikat „There is already an object named ‘tutaj_nazwa_obiektu’ in the database”. Dobrą praktyką jest nazywanie obiektów np. z przedrostkami np. Proc_ dla procedur lub View_ dla widoków itd.