SQL - procedury - plukasiewicz.net

Procedury

Procedury składowane to nic innego jak przygotowany kod SQL, który może być wielokrotnie używany. Jeżeli więc masz na myśli zapytanie, które piszesz wielokrotnie warto zapisać je jako procedurę składowaną, którą będzie gotowa do wielokrotnego użytku za każdym razem kiedy będzie Ci potrzebna.

Oprócz uruchamiania wielokrotnie tego samego kodu SQL będziesz miał możliwość przekazania parametrów do zapisanej procedury – w zależności od przekazanych parametrów wynik działania takiej procedury będzie inny.

Tworzenie prostej procedury

Zanim utworzysz procedurę składowaną muszisz wiedzieć jaki jest wynik końcowy, niezależnie czy wybierasz dane, czy chcesz je wstawić itd.

W tym prostym przykładzie pobierzemy wszystkie dane z tabeli Person.Address

SELECT * FROM Person.Address

Możemy teraz przejść do utworzenia pierwszej procedury:

CREATE PROCEDURE dbo.uspGetAddress
AS
SELECT * FROM Person.Address
GO

Wynikiem działania powyższej procedury jest zwrócenie wszystkich danych ze wspomnianej wyżej tabeli. Wywołanie wygląda w następujący sposób:

EXEC dbo.uspGetAddress
-- lub:
EXEC uspGetAddress
-- albo:
uspGetAddress

Podczas tworzenia procedury możesz użyć polecenia CREATE PROCEDURE lub CREATE PROC. Po nazwie procedury należy użyć słowa kluczowego AS a reszta to zwykły kod SQL.

Warto mieć na uwadzę, że w procedurze składowanej nie można używać słowa kluczowego GO. Kiedy kompilator SQL zauważy to słowo kluczowe uzna, że jest to koniec naszej procedury.

Używanie zmiany kontekstu bazy danych również nie jest dopuszczalne: USE dbName. Powodem jest to, że część procedury byłaby traktowana jako osobny pakiet a z definicji jest to zbiór jednej serii poleceń.

Tworzenie procedury z parametrami

Prawdziwą potęgą procedur składowanych jest możliwość przekazywania parametrów i oczekiwania różnych wyników – związane jest to z odpowiednim przygotowaniem zapytań.

Pojedynczy parametr

W tym przykładzie przygotujemy zapytanie do tabeli Person.Address, które będzie nam zwracało wyniki ograniczone jedynie do konkretnego miasta. Wymaga to zatem przekazania do procedury jednego, dodatkowego parametru:

CREATE PROCEDURE dbo.uspGetAddressByCity @City nvarchar(30)
AS
SELECT * FROM Person.Address
where City = @City
GO

W tym przypadku wywołanie przedstawia się w następujący sposób:

EXEC dbo.uspGetAddressByCity @City = 'New York'

Możemy oczywiście dowolnie przygotować naszą instrukcję. Na poniższym przykładzie możecie zobaczyć modyfikację, która daje użytkownikom punkt wyjścia do wyszukiwanych danych:

CREATE PROCEDURE dbo.uspGetAddressByCity @City nvarchar(30)
AS
SELECT * FROM Person.Address
where City  LIKE @City + '%'
GO

Wywołanie procedury wygląda dokładnie tak jak w powyższym przypadku przy czym wyniki będą się znacznie różniły.

W obu przypadkach silnik bazy danych zakłada, że parametr zostanie przekazany. Jeżeli nie, możecie spodziewać się poniższego komunikatu błędu:

1
2
3

EXEC dbo.uspGetAddressByCity
-- Msg 201, Level 16, State 4, Procedure uspGetAddressByCity, Line 0
-- Procedure or function 'uspGetAddressByCity' expects parameter '@City', which was not supplied.

Domyślna wartość parametru

W większości przypadków dobrą praktyką jest przekazywanie wartości NULL, nie zawsze jednak jest to możliwe. W poniższym przykładzie pokażę Wam jak przekazać domyślną wartość tak aby nie było konieczności określania wartości dla parametru oczekiwanego przez procedurę. Jeżeli przygotujemy i uruchomimy taką procedurę nie zostaną zwrócone żadne dane ponieważ zapytanie będzie poszukiwało wartości NULL dla kolumny City:

CREATE PROCEDURE dbo.uspGetAddressNullParam @City nvarchar(30) = NULL
AS 
SELECT * FROM Person.Address
where City = @City
GO

Możemy również wprowadzić pewną modyfikację polegającą na użyciu sprawdzenia ISNULL. W takim wypadku jeżeli zostanie przekazana jakaś wartość pozwoli ona na zwrócenie określonych wyników. Jeżeli parametr nie zostanie przekazany dojdzie do zwrócenia wszystkich rekordów. Warto mieć na uwadzę fakt, że w przypadku kiedy kolumna City posiada wartość NULL to taka wartość nie zostanie włączona w wynik operacji. Będzie należało dodać sprawdzenie ISNULL na tej kolumnie:

CREATE PROCEDURE dbo.uspGetAddressNullParam @City nvarchar(30) = NULL
AS 
SELECT * FROM Person.Address
where City = ISNULL(@City, City)
GO

Wiele parametrów

Przekazywanie wielu parametrów nie jest skomplikowane. Wystarczy podać każdy parametr oraz jego typ oddzielone od siebie przecinkiem tak jak zostało to pokazane poniżej:

CREATE PROCEDURE dbo.uspGetAddressMultipleParams @City nvarchar(30) = NULL, @AddresLine1 nvarchar(60) = NULL
AS 
SELECT * FROM Person.Address
where City = ISNULL(@City, City)
and AddressLine1 LIKE '%' + ISNULL(@AddresLine1, AddressLine1) + '%'
GO

Zwracanie parametrów z procedur

W poprzednich podrozdziałach skupiliśmy się na przekazywaniu parametrów do procedur. Odwrotną sytuajcą jest przekazywanie wartości parametrów z procedury składowanej. Jedynym z przykładów może być wywołanie kolejnej procedury, która nie zwraca żadnych danych ale zwraca wartości parametrów, które mogą być użyte przez inną procedurę.

Definiowanie takich parametrów jest bardzo podobne do parametrów wejściowych z taką różnicą, że musimy użyć słowa kluczowego OUTPUT. Zamiennie można używać słowa kluczowego OUT:

CREATE PROCEDURE dbo.uspGetAddressCount @City nvarchar(30) = NULL, @AddressCount int OUT
AS 
SELECT @AddressCount = COUNT(*)
FROM Person.Address
where City = @City
GO

Wywołanie takiej procedury wygląda w następujący sposób:

1
2
3

DECLARE @AddressCount INT
EXEC dbo.uspGetAddressCount @City='New York', @AddressCount = @AddressCount OUTPUT
SELECT @AddressCount

Powyższa procedura może być również wywołana bez podawania nazw parametrów:

1
2
3

DECLARE @AddressCount INT
EXEC dbo.uspGetAddressCount 'New York', @AddressCount OUTPUT
SELECT @AddressCount