Lekcja 33. Procedury składowane

Procedury składowane są obiektami przechowywanymi w bazie danych które hermetyzują kod. SQL Serwer używa kilku typów procedur składowanych:

Procedury napisane w T-SQL.
Procedury CLR napisane w językach Microsoft .NET.
Zewnętrzne procedury składowane odwołujące się do skompilowanych zewnętrznie procedur języka definicji danych (DLL).

Zrozumienie procedur składowanych

Procedury składowane T-SQL są napisane jako jedna partia kodu T-SQL. Posiadają one wiele ważnych cech:

Procedury mogą być wywoływane z kodu T-SQL za pomocą komendy EXECUTE.
Można używać w nich parametrów wejściowych i wyjściowych.
Mogą zwracać zbiór wynikowy zapytań do wywołującego je klienta.
Mogą modyfikować dane.
Mogą tworzyć, modyfikować i usuwać tabele i indeksy.

Największymi zaletami używania procedur składowanych są:

Enkapsulacja kodu T-SQL. Każda procedura może być wywoływana z wielu miejsc z parametrami które spowodują inne warunki jej wykonania.
Sprawiają, że baza danych jest bardziej bezpieczna.
- Można ograniczyć dostęp użytkowników do procedur składowanych.
- Procedury pomagają zapobiegać wstrzykiwaniu kodu SQL poprzez parametryzację.
Prezentują uniwersalną warstwę dostępu do danych dla użytkowników i aplikacji.
- Procedury pozwalają ominąć skomplikowaną logikę aby osiągnąć żądany wynik.
- Struktura bazy danych oraz procedur może ulec zmianie a użytkownik nie musi o tym wiedzieć ponieważ procedura stanowi dla niego interfejs dostępu do danych.
Pomagają polepszyć wydajność poprzez tworzenie planów zapytań które mogą być użyte ponownie.
- Plany zapytania dla wartości parametrów procedury są zapisywane i mogą być użyte ponownie zapobiegając rekompilacji kodu T-SQL.
- Procedury składowane mogą zredukować ilość danych przesyłanych przez sieć. Gdy aplikacja wykonuje całą pracę to wyniki oraz żądania do serwera musza być przesyłane przez sieć.

Procedury składowane mogą enkapsulować logikę aplikacji pracującą z danymi oraz z funkcjami administracyjnymi jak kopie zapasowe i przywracanie baz danych. Jednakże należny pamiętać o następujących kwestiach definiując procedurę:

Nie można używać komendy USE <database name>.
Nie można używać instrukcji CREATE AGGREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE lub VIEW.
Można tworzyć, modyfikować i usuwać tabele i indeksy za pomocą komend CREATE, ALTER i DROP.

Rozważmy kod T-SQL który pobiera wszystkie zamówienia klienta o ID równym 37 które miały miejsce w drugim kwartale 2007:

USE TSQL2012;
GO
SELECT orderid, custid, shipperid, orderdate, requireddate, shippeddate
FROM Sales.Orders
WHERE custid = 37
AND orderdate >= '2007-04-01'
AND orderdate < '2007-07-01';

Ten kod jest ograniczony. Aby sprawić żeby stał się bardziej uniwersalny możemy użyć zmiennych w miejscu literałów w klauzuli WHERE jak poniżej:

USE TSQL2012;
GO
DECLARE @custid AS INT,
@orderdatefrom AS DATETIME,
@orderdateto AS DATETIME;
SET @custid = 37;
SET @orderdatefrom = '2007-04-01';
SET @orderdateto = '2007-07-01';
SELECT orderid, custid, shipperid, orderdate, requireddate, shippeddate
FROM Sales.Orders
WHERE custid = @custid
AND orderdate >= @orderdatefrom
AND orderdate < @orderdateto;
GO

Wszystko co zrobiliśmy to zadeklarowaliśmy trzy zmienne i przypisaliśmy im wartości przed wykonaniem zapytania. Aby móc używać takiego kodu wielokrotnie można zapisać go jako procedurę składowaną jak poniżej:

IF OBJECT_ID('Sales.GetCustomerOrders', 'P') IS NOT NULL
DROP PROC Sales.GetCustomerOrders;
GO
CREATE PROC Sales.GetCustomerOrders
@custid AS INT,
@orderdatefrom AS DATETIME = '19000101',
@orderdateto AS DATETIME = '99991231',
@numrows AS INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT orderid, custid, shipperid, orderdate, requireddate, shippeddate
FROM Sales.Orders
WHERE custid = @custid
AND orderdate >= @orderdatefrom
AND orderdate < @orderdateto;
SET @numrows = @@ROWCOUNT;
RETURN;
END
GO

Wywołanie zapisanej procedury składowanej wygląda tak:

DECLARE @rowsreturned AS INT;
EXEC Sales.GetCustomerOrders
@custid = 37,
@orderdatefrom = '20070401',
@orderdateto = '20070701',
@numrows = @rowsreturned OUTPUT;
SELECT @rowsreturned AS "Rows Returned";
GO

Wynik jest taki sam. Dodatkowo otrzymaliśmy liczbę zwróconych wierszy za pomocą specjalnego parametru wyjściowego.

Sprawdzanie czy procedura składowana istnieje

Jeżeli tworzymy procedurę składowaną i okaże się, że procedura o takiej nazwie już istnieje to komenda CREATE zakończy się błędem. Jeżeli procedura już istnieje to można zaktualizować jej definicję za pomocą komendy ALTER, lecz jeżeli spróbujemy użyć komendy ALTER dla nieistniejącej procedury to komenda ta zakończy się błędem. Aby zapobiec takim zdarzeniom możemy sprawdzić czy procedura o danej nazwie już istnieje i zdecydować co chcemy zrobić w takim przypadku. Skrypt poniżej sprawdza czy procedura istnieje i jeżeli tak to usuwa ją za pomocą instrukcji DROP:

IF OBJECT_ID('Sales.GetCustomerOrders', 'P') IS NOT NULL
DROP PROC Sales.GetCustomerOrders;
GO

Istnieją również inne sposoby testowania istnienia obiektów bazy danych np sprawdzenie metadanych w sys.objects.

Parametry procedur składowanych

Procedury składowane mogą posiadać parametry których deklaracja jest bardzo podobna do deklaracji zmiennych. Przeanalizujmy instrukcję tworzenia procedury poniżej:

CREATE PROC Sales.GetCustomerOrders
@custid AS INT,
@orderdatefrom AS DATETIME = '19000101',
@orderdateto AS DATETIME = '99991231',
@numrows AS INT = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT orderid, custid, shipperid, orderdate, requireddate, shippeddate
FROM [Sales].[Orders]
WHERE custid = @custid
AND orderdate >= @orderdatefrom
AND orderdate < @orderdateto;
SET @numrows = @@ROWCOUNT;
RETURN;
END

Tworząc procedurę używamy komendy CREATE PROCEDURE lub skrutu CREATE PROC, po czym deklarujemy nazwę schematu bazy danych oraz nazwę procedury. Po nazwie procedury deklarujemy listę parametrów procedury wg poniższych wskazówek:

Nie trzeba deklarować parametrów w procedurze ale jeżeli je deklarujemy to muszą znajdować się one na początku.
Parametry mogą być wymagane lub opcjonalne.
- Jeżeli nie wstawimy wartości inicjalizującej to parametr jest wymagany - @custid AS INT w przykładzie.
- jeżeli wstawimy wartość inicjalizującą to parametr jest opcjonalny - np @orderdatefrom AS DATETIME = '19000101' w przykładzie.
Parametry procedury są traktowane jak zmienne dla całego ciała procedury.
Można inicjalizować wartości parametrów tak samo jak wartości zmiennych.
Można deklarować parametry wyjściowe za pomocą słowa kluczowego OUTPUT. Parametry wyjściowe są zawsze parametrami opcjonalnymi.
Po deklaracji parametrów trzeba rozpocząć blok ciała procedury za pomocą instrukcji AS.

BEGIN/END

Ciało procedury można otoczyć blokiem BEGIN/END dzięki czemu kod jest bardziej czytelny.

SET NOCOUNT ON

W ciele procedury składowanej ustawia się SET NOCOUNT ON aby usunąć komunikaty typu (3 row(s) affected) zwracane w trakcie wykonywania procedury. Ustawienie SET NOCOUNT ON może zwiększyć wydajność wykonywania procedury ponieważ zmniejsza się ilość komunikatów przesyłanych przez sieć.

RETURN i RETURN CODES

Procedura składowana kończy się kiedy kończy się batch T-SQL lecz można celowo zakończyć procedurę poprzez użycie komendy RETURN. Komendę RETURN można użyć więcej niż raz w procedurze. komenda ta zatrzyma wykonywanie procedury i przeniesie kontrolę wykonywania z powrotem do wywołującego klienta. Instrukcje po komendzie RETURN nie zostaną wykonane. Komenda RETURN powoduje, że SQL Serwer przesyła kod statusu do wywołującego klienta. Kod statusu ma wartość 0 dla sukcesu i liczbę ujemną jeżeli wystąpi błąd. Numery błędów nie są wiarygodne i nie należy na nich polegać. Zamiast tego należy użyć funkcji @@ERROR lub ERROR_NUMBER w bloku CATCH. Można przesyłać własne kody do klienta poprzez wstawienie wartości liczbowej do komendzie RETURN. Jednakże lepszą praktyką przekazywania informacji do wywołującego klienta jest użycie parametrów wyjściowych (OUTPUT).

Wykonywanie procedur składowanych

Aby wykonać procedurę z kodu T-SQL trzeba użyć komendy EXECUTE (EXEC w skrócie). Jeżeli procedura nie ma parametrów to wykonujemy ją poprzez komendę EXECUTE i nazwę procedury jak poniżej:

EXEC sp_configure;

Kod ten wykonuje systemową procedurę sp_configure. Ponieważ jest to procedura bazy danych MASTER to można ją wykonać w każdej bazie danych. Jeżeli procedura jest pierwszą instrukcją w porcji wsadowej kodu T-SQL (lub jedyną) to nie trzeba używać komendy EXECUTE.

Parametry wejściowe

Kiedy procedura składowana posiada parametry to można wstawić do nich wartość poprzez wyliczenie ich wartości w odpowiedniej kolejności(takiej jak została zadeklarowana w trakcie deklarowania procedury) za nazwą procedury i oddzielenie ich przecinkami lub poprzez połączenie wartości z nazwą parametru. Jako przykład posłuży nam stworzona wcześniej procedura Sales.GetCustomerOrders . Wywołanie tej procedury poprzez wyliczenie wartości parametrów w odpowiedniej pozycji wygląda tak:

EXEC Sales.GetCustomerOrders 37, '20070401', '20070701';

Należy zauważyć, że pomineliśmy parametr wyjściowy który jest opcjonalny. Wywołanie procedury z nazwami i wartościami parametrów wygląda tak:

EXEC Sales.GetCustomerOrders @custid = 37, @orderdatefrom = '20070401',
@orderdateto = '20070701';

Kiedy używamy nazw parametrów to nieważna jest kolejność ich wymienienia. Takie wywołanie też będzie działało:

EXEC Sales.GetCustomerOrders
@orderdatefrom = '20070401',
@orderdateto = '20070701',
@custid = 37;
GO

Używanie nazw parametrów jest uważane za dobrą praktykę.

Parametry wyjściowe

Aby dodać parametr wyjściowy należy dodać słowo kluczowe OUTPUT (lub w skrócie OUT) po definicji parametru np:

CREATE PROC Sales.GetCustomerOrders
@custid AS INT,
@orderdatefrom AS DATETIME = '19000101',
@orderdateto AS DATETIME = '99991231',
@numrows AS INT = 0 OUTPUT
AS  /* ... rest of procedure*/

Aby pobrać wartość z parametru wyjściowego trzeba również użyć słowa kluczowego OUTPUT kiedy wykonujemy procedurę i definiujemy zmienną która ma przechwycić wartość zwracanego parametru np:

DECLARE @rowsreturned AS INT;
EXEC Sales.GetCustomerOrders
@custid = 37,
@orderdatefrom = '20070401',
@orderdateto = '20070701',
@numrows = @rowsreturned OUTPUT;
SELECT @rowsreturned AS 'Rows Returned';
GO

Rozgałęzienie logiki

T-SQL oferuje kilka instrukcji które możemy użyć do kontroli przebiegu wykonywania naszego kodu. Te instrukcje mogą być używane w skryptach T-SQL i są używane w procedurach. Oto lista podstawowych instrukcji pozwalających na kontrolowanie przebiegu wykonywania kodu:

IF/ELSE
WHILE ( z BREAK i CONTINUE)
WAITFOR
GOTO
RETURN

IF/ELSE

Konstrukcja IF/ELSE daje możliwość warunkowego wykonywania kodu. Wstawiamy wyrażenie po komendzie IF które jest sprawdzane i jeżeli w wyniku da wartość TRUE to wykonywany jest blok który po nim następuje. Możemy dodać opcjonalny blok ELSE w którym wykonywany jest kod jeżeli wyrażenie w bloku IF da wartość FALSE. Poniższy kod jest przykładem zastosowania:

DECLARE @var1 AS INT, @var2 AS INT;
SET @var1 = 1;
SET @var2 = 2;
IF @var1 = @var2
PRINT 'The variables are equal';
ELSE
PRINT 'The variables are not equal';
GO

Jeżeli instrukcje IF/ELSE występują bez bloków BEGIN/END to mogą zawierać tylko po jednej instrukcji w swoim ciele. Należy uważać aby nie napisać kodu jak poniżej:

DECLARE @var1 AS INT, @var2 AS INT;
SET @var1 = 1;
SET @var2 = 1;
IF @var1 = @var2
PRINT 'The variables are equal';
ELSE
PRINT 'The variables are not equal';
PRINT '@var1 does not equal @var2';
GO

Trzecia instrukcja PRINT jest poza zasięgiem działania komendy ELSE i zostanie wykonana bez względu na wynik wyrażenia @var1 = @var2.

WHILE

Dzięki instrukcji WHILE można wykonywać pętle w kodzie T-SQL trwające dopóki wyrażenie w pętli będzie miało wartości TRUE. Po słowie kluczowym WHILE występuje wyrażenie warunkowe które jest wyliczane po każdym wykonaniu pętli aż do momentu kiedy jego wartość będzie wynosiła FALSE. Po opuszczeniu pętli WHILE wykonywane są następne instrukcje kodu T-SQL. Poniżej przedstawiony jest przykład pętli WHILE która wykonuje się aż do osiągnięcia przez licznik wartości 10:

SET NOCOUNT ON;
DECLARE @count AS INT = 1;
WHILE @count <= 10
BEGIN
PRINT CAST(@count AS NVARCHAR);
SET @count += 1;
END;

Używając pętli WHILE należy pamiętać aby warunek pętli był skończony (tzn osiągał wartość FALSE). W przeciwnym wypadku pętla będzie wykonywana w nieskończoność. W pętli WHILE można użyć komendy BREAK która natychmiastowo kończy wykonywanie pętli oraz komendy CONTINUE która powoduje skok do początku pętli WHILE (kod T-SQL w ciele pętli po komendzie CONTINUE zostanie pominięty). Poniższy przykład prezentuje wykonanie WHILE z BREAK i CONTINUE:

SET NOCOUNT ON;
DECLARE @count AS INT = 1;
WHILE @count <= 100
BEGIN
IF @count = 10
BREAK;
IF @count = 5
BEGIN
SET @count += 2;
CONTINUE;
END
PRINT CAST(@count AS NVARCHAR);
SET @count += 1;
END;

Wydruk liczb 5 i 6 jest pominięty przez użycie komendy CONTINUE przed instrukcją PRINT. Pętla nie dociera do wykonania wydruku dla liczby 10 ponieważ użyta jest instrukcja BREAK.

WAITFOR

Komenda WAITFOR nie zmienia przepływu wykonania kodu T-SQL ale można jej użyć do zatrzymania wykonywania kodu na określony czas. WAITFOR posiada trzy opcje: WAITFOR DELAY, WAITFOR TIME i WAITFOR RECEIVE (WAITFOR RECEIVE jest używana tylko w Service Broker). WAITFOR DELAY powoduje opóźnienie wykonania na określony czas np poniższy kod opóźnia wykonanie o 20s:

WAITFOR DELAY '00:00:20';

WAITFOR TIME powoduje zatrzymanie wykonania do określonego czasu np poniższy kod czeka na wykonanie do 11:45:

WAITFOR TIME '23:46:00';

GOTO

Używając komendy GOTO można skoczyć do zdefiniowanej etykiety T-SQL. Cały kod który jest przeskoczony nie zostaje wykonany. Poniższy przykład przedstawia pominięcie drugiej instrukcji PRINT dzięki użyciu komendy GOTO:

PRINT 'First PRINT statement';
GOTO MyLabel;
PRINT 'Second PRINT statement';
MyLabel:
PRINT 'End';

Użycie GOTO nie jest rekomendowane ponieważ prowadzi do komplikacji kodu.

Opracowanie procedur składowanych

Programując procedury składowane trzeba mieć na uwadze kilka ważnych czynników.

Wynik procedury składowanej

Procedury składowane mogą zwracać wyniki do wywołującego je klienta. Zwracany wynik bazuje na zapytaniach wykonywanych przez procedurę. W rezultacie zwracany może być więcej niż jeden wynik. Poniższy kod prezentuje procedurę zwracającą dwa zbiory wynikowe:

IF OBJECT_ID('Sales.ListSampleResultsSets', 'P') IS NOT NULL
DROP PROC Sales.ListSampleResultsSets;
GO
CREATE PROC Sales.ListSampleResultsSets
AS
BEGIN
SELECT TOP (1) productid, productname, supplierid,
categoryid, unitprice, discontinued
FROM Production.Products;
SELECT TOP (1) orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails;
END
GO
EXEC Sales.ListSampleResultsSets

Innym rodzajem wyniku zwracanego przez procedury mogą być parametry wyjściowe OUTPUT.

Wywoływanie innych procedur składowanych

W procedurze składowanej można wywoływać inne procedury. W rzeczywistości, jest to powszechny sposób hermetyzacji i ponownego wykonywania kodu. jednakże trzeba zwracać uwagę na pewne szczegóły wywołując inne procedury:

Kiedy tworzysz tabelę tymczasową w jednej procedurze składowanej to jest ona widoczna dla procedur składowanych wywoływanych w tej procedurze. Jednakże tabela ta nie jest widoczna w procedurach wywołujących tę procedurę.
Zmienne zadeklarowane w procedurze nie są widoczne w procedurach wywoływanych przez tę procedurę.

Obsługa błędów w procedurach składowanych

Aby zabezpieczyć procedurę składowaną przed błędami można używać bloku TRY/CATCH oraz wyrażenia THROW tak samo jak w skryptach T-SQL.

Dynamiczny SQL w procedurach składowanych

Tak samo jak w skryptach T-SQL można używać dynamicznego kodu T-SQL. Kiedy procedury są używane przez użytkowników zewnętrznych należy zabezpieczyć je przed wstrzykiwaniem kodu SQL.

Ćwiczenia

Tworzenie procedur składowanych do wykonania zadań administracyjnych

Napisz pętlę WHILE iterującą po wszystkich niesystemowych bazach danych. Użyj komendy PRINT która będzie symulować wykonanie kopii zapasowej bazy danych.

	
DECLARE @databasename AS NVARCHAR(128);
SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name NOT IN
('master', 'model', 'msdb', 'tempdb'));
WHILE @databasename IS NOT NULL
BEGIN
PRINT @databasename;
SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name NOT IN
('master', 'model', 'msdb', 'tempdb') AND name > @databasename);
END
GO

Zmień skrypt tak aby nazwa pliku kopi zapasowej bazy danych miała format__.bak. Użyj aktualnego czasu i funkcji CONVERT() do konwersji daty na ciąg znaków.
```
SELECT CONVERT(NVARCHAR, GETDATE(), 120)
```

Usuń znaki "_" i ":" z nazwy kopii zapasowej.

SELECT REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,
GETDATE(), 120), ' ', '_'), ':', ''), '-', '');

Wstaw sformatowaną nazwę do instrukcji tworzącej kopię zapasową (i zamień komendę PRINT komendą BACKUPDATABASE).

DECLARE @databasename AS NVARCHAR(128)
, @timecomponent AS NVARCHAR(50)
, @sqlcommand AS NVARCHAR(1000);
SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name
NOT IN ('master', 'model', 'msdb', 'tempdb'));
WHILE @databasename IS NOT NULL
BEGIN
SET @timecomponent = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,
GETDATE(), 120), ' ', '_'), ':', ''), '-', '');
SET @sqlcommand = 'BACKUP DATABASE ' + @databasename + ' TO DISK =
''C:\Backups\' + @databasename + '_' + @timecomponent + '.bak''';
PRINT @sqlcommand;
--EXEC(@sqlcommand);
SET @databasename = (SELECT MIN(name) FROM sys.databases WHERE name
NOT IN ('master', 'model', 'msdb', 'tempdb') AND name > @databasename);
END;
GO

Zamień skrypt w procedurę składowaną.

IF OBJECT_ID('dbo.BackupDatabases', 'P') IS NOT NULL
DROP PROCEDURE dbo.BackupDatabases
GOCREATE PROCEDURE dbo.BackupDatabases
AS
BEGIN
DECLARE @databasename AS NVARCHAR(128)
,@timecomponent AS NVARCHAR(50)
,@sqlcommand AS NVARCHAR(1000);

SET @databasename = (
SELECT MIN(NAME)
FROM sys.databases
WHERE NAME NOT IN (
'master'
,'model'
,'msdb'
,'tempdb'
)
);

WHILE @databasename IS NOT NULL
BEGIN
SET @timecomponent = REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR, GETDATE(), 120), ' ', '_'), ':', ''), '-', '');
SET @sqlcommand =
'BACKUP DATABASE ' + @databasename + ' TO DISK = ''C:\Backups\' + @databasename + '_' + @timecomponent + '.bak''';

PRINT @sqlcommand;

--EXEC(@sqlcommand);
SET @databasename = (
SELECT MIN(NAME)
FROM sys.databases
WHERE NAME NOT IN (
'master'
,'model'
,'msdb'
,'tempdb'
)
AND NAME > @databasename
);
END;

RETURN;
END;
GO

Projektowanie procedur składowanych służących do wstawiania danych

Użyj poniższego skryptu tworzącego prostą niezabezpieczoną procedurę wstawiającą dane.

USE TSQL2012;
GO
IF OBJECT_ID('Production.InsertProducts', 'P') IS NOT NULL
DROP PROCEDURE Production.InsertProducts
GO
CREATE PROCEDURE Production.InsertProducts
@productname AS NVARCHAR(40)
, @supplierid AS INT
, @categoryid AS INT
, @unitprice AS MONEY = 0
, @discontinued AS BIT = 0
AS
BEGIN
INSERT Production.Products (productname, supplierid, categoryid,
unitprice, discontinued)
VALUES (@productname, @supplierid, @categoryid, @unitprice,
@discontinued);
RETURN;
END;
GO

Przetestuj działanie procedury z poprawnymi parametrami.

EXEC Production.InsertProducts
@productname = 'Test Product'
, @supplierid = 10
, @categoryid = 1
, @unitprice = 100
, @discontinued = 0;
GO
-- Inspect the results
SELECT * FROM Production.Products WHERE productname = 'Test Product';
GO
-- Remove the new row
DELETE FROM Production.Products WHERE productname = 'Test Product';

Przetestuj działanie procedury z niepoprawnymi parametrami.
```
EXEC Production.InsertProducts
@productname = 'Test Product'
, @supplierid = 10
, @categoryid = 1
, @unitprice = -100
, @discontinued = 0
```
Wynik:
Msg 547, Level 16, State 0, Procedure InsertProducts, Line 9 The INSERT statement conflicted with the CHECK constraint "CHK_Products_unitprice". The conflict occurred in database "TSQL2012", table "Production.Products", column 'unitprice'.

Dodaj obsługę błędów do procedury.

IF OBJECT_ID('Production.InsertProducts', 'P') IS NOT NULL
DROP PROCEDURE Production.InsertProducts
GO
CREATE PROCEDURE Production.InsertProducts
@productname AS NVARCHAR(40)
, @supplierid AS INT
, @categoryid AS INT
, @unitprice AS MONEY = 0
, @discontinued AS BIT = 0
AS
BEGIN
BEGIN TRY
INSERT Production.Products (productname, supplierid, categoryid,
unitprice, discontinued)
VALUES (@productname, @supplierid, @categoryid,
@unitprice, @discontinued);
END TRY
BEGIN CATCH
THROW;
RETURN;
END CATCH;
END;
GO

Przetestuj działanie procedury z nieprawidłowymi danymi.
```
EXEC Production.InsertProducts
@productname = 'Test Product'
, @supplierid = 10
, @categoryid = 1
, @unitprice = -100
, @discontinued = 0
```
Wynik:
Msg 547, Level 16, State 0, Procedure InsertProducts, Line 10 The INSERT statement conflicted with the CHECK constraint "CHK_Products_unitprice". The conflict occurred in database "TSQL2012", table "Production.Products", column 'unitprice'.

Dodaj do procedury testowanie wstawianych parametrów.

IF OBJECT_ID('Production.InsertProducts', 'P') IS NOT NULL
DROP PROCEDURE Production.InsertProducts
GO
CREATE PROCEDURE Production.InsertProducts
@productname AS NVARCHAR(40)
, @supplierid AS INT
, @categoryid AS INT
, @unitprice AS MONEY = 0
, @discontinued AS BIT = 0
AS
BEGIN
DECLARE @ClientMessage NVARCHAR(100);
BEGIN TRY
-- Test parameters
IF NOT EXISTS(SELECT 1 FROM Production.Suppliers
WHERE supplierid = @supplierid)
BEGIN
SET @ClientMessage = 'Supplier id '
+ CAST(@supplierid AS VARCHAR) + ' is invalid';
THROW 50000, @ClientMessage, 0;
END
IF NOT EXISTS(SELECT 1 FROM Production.Categories
WHERE categoryid = @categoryid)
BEGIN
SET @ClientMessage = 'Category id '
+ CAST(@categoryid AS VARCHAR) + ' is invalid';
THROW 50000, @ClientMessage, 0;
END;
IF NOT(@unitprice >= 0)
BEGIN
SET @ClientMessage = 'Unitprice '
+ CAST(@unitprice AS VARCHAR) + ' is invalid. Must be >= 0.';
THROW 50000, @ClientMessage, 0;
END;
-- Perform the insert
INSERT Production.Products (productname, supplierid, categoryid,
unitprice, discontinued)
VALUES (@productname, @supplierid, @categoryid, @unitprice, @discontinued);
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
GO

Przetestuj działanie procedury z nieprawidłowymi danymi.
```
EXEC Production.InsertProducts
@productname = 'Test Product'
, @supplierid = 10
, @categoryid = 1
, @unitprice = -100
, @discontinued = 0
```
Wynik:
Msg 50000, Level 16, State 0, Procedure InsertProducts, Line 30 Unitprice -100.00 is invalid. Must be >= 0.

Podsumowanie

Procedury T-SQL są modułami w których przechowuje się wykonywalny kod T-SQL w bazie danych. Procedury są obiektami bazy danych.
Procedury składowane hermetyzują kod T-SQL po stronie serwera przez co redukują obciążenie sieci i mogą być stosowane jako warstwa dostępu do danych dla aplikacji lub do wykonywania zadań administracyjnych.
Procedury składowane mogą być definiowane z użyciem parametrów. Parametry te mogą być wejściowe i wyjściowe. Parametry mogą być zadeklarowane z domyślną wartością.
Parametry procedury są definiowane w taki sam sposób jak zmienne T-SQL i mogą być wywoływane i zmieniane tak samo jak zmienne.
Każda procedura może mieć tylko jedną partię kodu (jeden batch).
Procedury mogą wywoływać inne procedury.
Kiedy w procedurze zostanie wykonana komenda RETURN to wykonywanie procedury jest przerwane i wyniki procedury są zwracane do klienta.
Procedury mogą zwracać więcej niż jeden zbiór wynikowy.