Tworzenie procedur przechowywanych (aparat bazy danych)

Tworzenie procedur przechowywanych, stosując procedurę tworzenia Transact-SQL instrukcja.

Przed utworzeniem procedura składowana, uznają, że:

• Nie można łączyć instrukcji CREATE PROCEDURE z innych instrukcji SQL w jednej partia.

• Aby utworzyć procedury, musi mieć uprawnienia CREATE PROCEDURE w bazie danych i ZMIEŃ na schemacie, w którym tworzona jest procedura.Procedury przechowywane CLR musi albo własnych wirtualny plik dziennika , do którego odwołuje się <method_specifier>, lub mieć uprawnienie odwołania do wirtualny plik dziennika.

• Procedury przechowywane są obiekty o zakresie schematu, a ich nazwy muszą spełniać zasady dla identyfikatorów.

• procedura składowana można utworzyć tylko w bieżącej bazie danych.

Podczas tworzenia procedura składowana, należy określić:

• Danych wejściowych parametry i parametrów wyjściowych do wywołania procedury lub programu partia.

• Instrukcje programowania, wykonujących operacje w bazie danych, łącznie z wywołaniem innych procedur.

• Wartość stanu zwrócony do wywołania procedury lub partia oznacza sukces lub Niepowodzenie (i przyczynę awarii).

• Instrukcje obsługa błędów potrzebne do połowu i obsługi potencjalnych błędów.

  Błąd przekazywanie funkcji, takich jak ERROR_LINE i ERROR_PROCEDURE można określić w procedura składowana.Aby uzyskać więcej informacji, zobacz za pomocą SPRÓBOWAĆ...POŁÓW w języku Transact-SQL.

Nazewnictwo procedur przechowywanych

Firma Microsoft zaleca, aby nie utworzyć procedury przechowywane, przy użyciu sp_ jako prefiks.SQL Serverużywa sp_ prefiks do wyznaczenia procedury składowane w systemie.Wybrana nazwa może powodować konflikt z niektóre procedury przyszłego systemu.Jeśli aplikacja używa nonschema kwalifikowaną nazwę odwołania, nazwę procedury konflikt z nazwą procedury systemu aplikacji zostaną przerwane, ponieważ nazwa wiąże się z procedurą systemu nie własne.

Zdefiniowany przez użytkownika procedura składowana , która ma taką samą nazwę jak systemową procedura składowana i jest nonqualified lub w dbo Schemat nigdy nie będzie wykonywane; system procedura składowana będzie zawsze wykonać zamiast.Poniższy przykład ilustruje to zachowanie.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Za pomocą schematu jawne kwalifikator zapewnia także korzyści nieznaczne wydajności.Rozpoznawanie nazw jest trochę szybszy Jeśli Aparat baz danych nie trzeba przeszukiwać wiele schematów, aby znaleźć procedurę.Aby uzyskać więcej informacji, zobacz wykonywanie procedury przechowywanej.

Tymczasowe procedury przechowywane

Prywatne i globalne tymczasowe procedury przechowywane, analogiczne do tabel tymczasowych mogą być tworzone z # i dodawany do nazwy procedury ## prefixes.# oznacza lokalny tymczasowe procedura składowana; ## oznacza globalne tymczasową procedura składowana.Te procedury nie istnieją po SQL Server jest shut niedziałający.

Tymczasowe procedury przechowywane są przydatne przy łączeniu się z wcześniejszych wersji programu SQL Server nie obsługują ponownego użycia planów wykonania dla Transact-SQL instrukcje lub partii.Łączenie się z aplikacjami SQL Server 2000 i wyższej powinny używać sp_executesql systemu procedura składowana zamiast tymczasowych procedur przechowywanych.Tylko połączenie lokalne tymczasową procedurę tworzenia można, wykonać i procedury jest automatycznie usuwany po zamknięciu połączenia.

Dowolne połączenie można wykonać globalnego tymczasową procedura składowana.Globalne tymczasową procedura składowana istnieje aż zamknięte połączenie używane przez użytkownika, który utworzył procedury i wykonywane są aktualnie wersjach procedury przez innych połączeń.Po zamknięciu połączenia, który został użyty do utworzenia procedury jest dozwolone nie dalszej realizacji globalnego tymczasową procedura składowana .Aby ukończyć są dozwolone tylko tych połączeń, które zostało już rozpoczęte, wykonywanie procedura składowana .

Jeśli procedura składowana nie prefiksem # lub ## jest tworzony bezpośrednio w programie tempdb bazy danych, procedura składowana jest automatycznie usuwany po SQL Server kończy pracę niedziałający ponieważ tempdb jest utworzony ponownie za każdym czas SQL Server jest uruchomiona.Procedury tworzone bezpośrednio w tempdb Istnieje nawet po tworzenia połączenie jest przerywane.

Ostrzeżenie

Użycie tymczasowych procedur przechowywanych, można utworzyć rywalizacja na tabele systemowe w tempdb i negatywnie wpływać na wydajność.Zalecane jest sp_executesql Zamiast używać.sp_executesql Nie przechowywać dane w tabele systemowe i w związku z tym pozwala uniknąć tego problemu.

Nie można utworzyć procedury przechowywane CLR jako tymczasowych procedur przechowywanych.

Przykłady

A.Prostą procedurę przy użyciu wybierz złożonych

Poniższa procedura składowana zwraca wszystkich pracowników (imion i nazwisk dostarczone), ich tytuły i ich nazwy działów z widoku.Ta procedura składowana nie używa żadnych parametrów.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

uspGetEmployeesprocedura składowana mogą być wykonywane w następujący sposób:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B.Prostą procedurę przy użyciu parametrów

Poniższa procedura składowana zwraca tylko określonego pracownika (imię i nazwisko dostarczone), jej tytuł i jej nazwę działu z widoku.Ta procedura składowana akceptuje dokładne odpowiedniki parametry przekazywane.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

uspGetEmployeesprocedura składowana mogą być wykonywane w następujący sposób:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C.Prostą procedurę przy użyciu symboli wieloznacznych parametrów

Poniższa procedura składowana zwraca tylko określonych pracowników (imion i nazwisk dostarczone), ich tytuły i ich departamentów z widoku.Tego wzorca procedura składowana pasuje do parametrów przekazanych lub, jeżeli nie jest podany, wykorzystuje ustawienia domyślne (ostatnia nazwy zaczynające się od litery D).

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

uspGetEmployees2 procedura składowana mogą być wykonywane w wielu kombinacji.Tylko kilka kombinacji przedstawiono poniżej:

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D.Przy użyciu parametrów wyjściowych

Poniższy przykład tworzy uspGetList procedura składowana, która zwraca listę produktów, których ceny, które nie przekraczają określonej ilości.W przykładzie przy użyciu wielu instrukcji SELECT i wiele parametrów wyjściowych.Parametry wyjściowe umożliwiają procedury zewnętrznego, partialub więcej niż jedną Transact-SQL instrukcja dostępu wartość zestaw podczas wykonywania procedury.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Wykonanie uspGetList , aby powrócić do listy Adventure Works produktów (Rowery), które kosztują mniej niż 700 $.Parametry wyjściowe @ Kosztów i @ ComparePrices są używane z język sterowania przepływem do zwracania wiadomości w wiadomości okna.

Ostrzeżenie

Zmienną produkcji musi być zdefiniowany podczas tworzenia procedury, jak również podczas użytkowania zmiennej.Parametr Nazwa i nazwa zmiennej nie muszą odpowiadać; Jednakże typ danych i pozycjonowania parametru musi odpowiadać (chyba że @ ListPrice= variablejest używana).

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Poniżej przedstawiono częściową zestaw wyników:

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

Zobacz także

Zadania

Jak: Tworzenie procedury składowanej (SQL Server Management Studio)

Odwołanie

CREATE PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

WYKONANIE klauzuli (Transact-SQL)

PRZYWRÓĆ (Transact-SQL)

Koncepcje

Wycofywanie zmian i zatwierdzenia procedur przechowywanych i wyzwalaczy

Tworzenie CLR procedur przechowywanych

Wykonywanie przechowywanych procedur (aparat bazy danych)

Modyfikacja procedur przechowywanych

Ponownej kompilacji procedury przechowywanej

Wyświetlanie procedur przechowywanych.

Usuwanie procedur przechowywanych

Inne zasoby

Kontekst przełączania (aparat bazy danych)

Określanie parametrów

Zwracanie danych z procedury przechowywanej