CREATE PROCEDURE (Transact-SQL)

Składnia

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

--CLR Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Argumenty

• schema_name
  Nazwa schematu, do której należy procedury.Procedury są związanych ze schematem.Jeśli nie określono nazwy schematu, gdy tworzona jest procedura, zostanie automatycznie przypisany domyślnego schematu użytkownik, który tworzy procedurę.Aby uzyskać więcej informacji na temat schematów, zobacz Oddzielanie schematu użytkownika.

• procedure_name
  Nazwa procedury.Nazwy procedury muszą być zgodne z zasadami identyfikatorów i musi być unikatowa w schemacie.

  Należy unikać stosowania sp_ prefiksu w nazwach procedur.Ten prefiks jest używany przez SQL Server do wyznaczenia procedur systemu.Przy użyciu prefiksu może spowodować kod aplikacji podziału, jeśli istnieje procedura systemu o tej samej nazwie.Aby uzyskać więcej informacji, zobacz Zaprojektowanie procedury przechowywane (aparat bazy danych).

  Lokalna czy globalna tymczasowe procedury mogą być tworzone przy użyciu jednego numeru (#) przed procedure_name ()#procedure_name) dla lokalnego tymczasowe procedury i dwa znaki numeru dla globalnego tymczasowe procedury (##procedure_name).Lokalne tymczasową procedurę jest widoczna tylko dla połączenia, które utworzył i usunięte po zamknięciu tego połączenia.Globalne tymczasową procedurę jest dostępna dla wszystkich połączeń i jest przenoszony na końcu ostatniej sesja przy użyciu procedury.Nie można określić nazwy tymczasowej procedury CLR.

  Nazwę procedury lub globalnego tymczasową procedurę, włączając ##, nie może przekraczać 128 znaków.Pełna nazwa lokalnego tymczasową procedurę, włączając #, nie może przekraczać 116 znaków.

• **;**number
  Opcjonalna liczba całkowita, która jest używany do grupowania procedury o tej samej nazwie.Procedury te zgrupowane można upuszczać razem przy użyciu jednej procedury UPUŚĆ instrukcja.

  Ostrzeżenie

  Ta funkcja zostanie usunięta z przyszłej wersji programu Microsoft SQL Server. Należy unikać stosowania tej funkcji w nowych projektach oraz zaplanować modyfikację aplikacji, w których obecnie jest używana ta funkcja.

  Numerowane procedury nie można używać xml lub CLR zdefiniowane przez użytkownika typów i nie można używać w przewodnik planu.

• @parameter
  Parametr zadeklarowane w procedurze.Określ nazwę parametru za pomocą znaku (@) jako pierwszego znaku.Nazwa parametru musi być zgodne z zasadami identyfikatorów.Parametry są lokalne procedury; tej samej nazwy parametru mogą być używane w innych procedur.

  Może być deklarowana na jeden lub więcej parametrów; maksymalna to 2,100.Wartość każdego parametru zadeklarowanej musi być podany przez użytkownika wywołania procedury, o ile nie zostanie określona wartość domyślna dla parametru lub wartość jest zestaw na równą inny parametr.Jeśli procedura składa się z tabela-wartości parametrówi Brak parametru w wywołaniu, przekazany pustej tabela .Parametry mogą mieć miejsce jedynie wyrażeń stała ; nie można używać zamiast nazwy tabela , nazwy kolumna lub nazw innych obiektów bazy danych.Aby uzyskać więcej informacji, zobacz EXECUTE (Transact-SQL).

  Nie można deklarować parametrów, jeśli określony dla replikacji.

• [ type_schema_name**.** ] data_type
  Typ danych parametru i schematu, do której należy typ danych.

  Wytyczne dla Transact-SQL procedury:

  • Wszystkie Transact-SQL typy danych mogą być używane jako parametry.

  • Typ zdefiniowany przez użytkownika tabela można użyć do utworzenia tabela-wartości parametrów.Parametry wartościami przechowywanymi w tabeli można tylko parametry wejściowe i musi towarzyszyć słowo kluczowe tylko do odczytu.Aby uzyskać więcej informacji, zobacz Parametry oródwierszową (aparat bazy danych).

  • cursortypy danych mogą być tylko parametry wyjściowe oraz muszą towarzyszyć VARYING słowa kluczowego.

  Wytyczne dotyczące procedur CLR:

  • Wszystkie macierzystego SQL Server typy danych, które mają odpowiedniki w kod zarządzany mogą być używane jako parametry.Aby uzyskać więcej informacji dotyczących zgodności między typami CLR i SQL Server typów danych, zobacz Mapowanie danych parametru CLR.Aby uzyskać więcej informacji o SQL Server typów danych i ich składni, zobacz Typy danych (Transact-SQL).

  • Wartościami przechowywanymi w tabeli lub cursor typów danych nie można używać jako parametry.

  • Jeśli typ danych parametru jest zdefiniowany przez użytkownika typ CLR, musi mieć uprawnienie EXECUTE do typu.

• RÓŻNE
  Określa zestaw wyników obsługiwany jako parametr wyjściowy.Ten parametr jest konstruowana dynamicznie w procedurze i jego zawartość może być różna.Dotyczy wyłącznie cursor Parametry.Ta opcja nie jest ważne dla procedury CLR.

• default
  Wartość domyślna dla parametru.Jeśli zdefiniowano domyślną wartość parametru, procedury mogą być wykonywane bez określenia wartości parametru.Wartość domyślna musi być stała lub może mieć wartość NULL.stała wartość może być w postaci symbolu wieloznacznego, dzięki czemu można użyć słowa kluczowego PODOBNEGO przy przekazywaniu parametr do procedury.Zobacz przykład c poniżej.

  Wartości domyślne są rejestrowane w sys.parameters.default kolumna tylko dla procedury CLR.kolumna będzie mieć wartość NULL dla Transact-SQL Parametry procedury.

• POZA | DANE WYJŚCIOWE
  Wskazuje, że parametr jest parametrem wyjściowym.Parametry wyjściowe służy do zwracania wartości do wywołującego procedurę. text, ntext, i image parametrów nie można używać jako parametry wyjściowe, chyba że procedurą jest procedura CLR.Parametr wyjściowy może być zastępczy kursor , chyba że procedurą jest procedura CLR.tabela-nie można określić typ danych wartości jako parametru WYJŚCIOWEGO procedury.

• TYLKO DO ODCZYTU
  Wskazuje, że parametr nie zaktualizowane, lub modyfikacji w treści tej procedury.Jeśli tabelajest typem parametru-wartość typu, musi być określona tylko do odczytu.

• KOMPILUJ PONOWNIE
  Wskazuje, że Aparat baz danych jest nie pamięci podręcznej plan kwerend dla tej procedury, jest ona wymuszania skompilowany każdego czas jest wykonywany.Aby uzyskać więcej informacji dotyczących powodów wymuszanie ponowną kompilację, zobacz Ponownej kompilacji procedury przechowywanej.Tej opcji nie używać, gdy określony dla replikacji lub procedur CLR.

  Aby Aparat baz danych odrzucenie planów kwerend dla wykonania poszczególnych kwerend wewnątrz procedury, należy użyć wskazówki dotyczącej kwerendy RECOMPILE w definicji kwerendy.Aby uzyskać więcej informacji, zobacz Wskazówki kwerendy (Transact-SQL).

• SZYFROWANIE
  Wskazuje, że SQL Server przekonwertuje oryginalny tekst instrukcja CREATE PROCEDURE posiadający mylącą format.Wyjście zaciemniania nie jest bezpośrednio widoczne w innych widoki wykazu w SQL Server.Użytkownicy, którzy nie mają dostępu do plików tabele systemowe lub bazy danych nie można pobrać posiadający mylącą tekstu.Jednak tekst będzie dostępny uprzywilejowanych użytkowników, którzy mieli dostęp albo tabele systemowe przez DAC portu lub bezpośrednio uzyskać dostępu do plików bazy danych.Ponadto użytkownicy, którzy mogą dołączać debuger do procesu serwera można pobrać odszyfrowane procedury z pamięci w czasie wykonywania. Aby uzyskać więcej informacji dotyczących uzyskiwania dostępu do systemu metadane, zobacz Konfiguracja widoczność metadanych.

  Ta opcja nie jest ważne dla procedury CLR.

  Procedury utworzone za pomocą tej opcji nie można opublikować jako część SQL Server replikacja.

• WYKONANIE JAKO
  Określa kontekst zabezpieczeń, w którym podczas wykonać procedury.

  Aby uzyskać więcej informacji, zobacz WYKONANIE klauzuli (Transact-SQL).

• DLA REPLIKACJI
  Określa, że procedura jest tworzony dla replikacja.W związku z tym nie można wykonać na subskrybenta.Procedury utworzone za pomocą opcji dla replikacji jest używany jako filtr procedury i jest wykonywana tylko podczas replikacja.Nie można deklarować parametrów, jeśli określony dla replikacji.Nie można określić dla replikacji dla procedury CLR.Opcja ponownej kompilacji jest ignorowana dla procedury utworzone za pomocą dla replikacji.

  Procedury dla replikacji ma typ obiektu RF w sys.objects i sys.procedures.

• { [ROZPOCZĄĆ] sql_statement [;] [ ...n ] [ END ] }
  Jeden lub więcej Transact-SQL sprawozdań zawierających treść procedury.Opcjonalne słowa kluczowe rozpoczęcia i zakończenia można użyć, należy ująć w sprawozdaniu.Dla informacji zobacz Najważniejsze wskazówki, uwagi ogólne i ograniczenia i ograniczenia podrozdziałach.

• Nazwa zewnętrznego assembly_name**.class_name.method_name
  Określa metoda .NET Framework wirtualny plik dziennika dla procedury CLR do odwołania.class_namemusi być prawidłową SQL Server identyfikatora i musi istnieć jako klasa w wirtualny plik dziennika.Jeśli klasa ma kwalifikowanej nazw nazwa używana jest kropka (.) do oddzielania części obszaru nazw, nazwa klasy musi rozdzielany za pomocą nawiasów kwadratowych (   ) lub cudzysłów ("** ").Określona metoda musi być statyczna metoda klasy.

  Domyślnie SQL Server nie może wykonać CLR kodu.Tworzenie, modyfikowanie i usuwanie obiektów bazy danych, które odwołują się do wspólnych moduły obsługi języka; Jednakże, nie możesz wykonać te odwołania w SQL Server do chwili włączenia clr włączona opcja.Aby włączyć opcję, użyj sp_configure.

Najważniejsze wskazówki

Chociaż nie jest wyczerpujący wykaz najważniejszych wskazówek, te sugestie może zwiększyć wydajność procedury.

• Użyj instrukcja SET NOCOUNT ON jako pierwsza instrukcja w treści tej procedury.Oznacza to, że umieścić zaraz po jako słowo kluczowe.Ta włącza off wiadomości, które SQL Server wyśle z powrotem do klient po SELECT, INSERT, UPDATE, korespondencji seryjnej i wykonaniu instrukcji DELETE.Ogólna poprzez wyeliminowanie tego dużego obciążenia sieci niepotrzebne lepsza wydajność bazy danych i aplikacji.Aby uzyskać informacje, zobacz Instrukcję SET NOCOUNT (Transact-SQL).

• Użyj nazwy schematu podczas tworzenia lub odwoływania się do obiektów bazy danych w procedurze.Trwa mniej przetwarzania czas Aparat baz danych do rozpoznawania nazw obiektu, jeśli nie trzeba przeszukiwać wiele schematów.Będzie również zapobiec uprawnienia i dostęp problemów powodowanych przez użytkownika domyślnego schematu jest przypisywany podczas tworzenia obiektów bez określenia schematu.Aby uzyskać więcej informacji, zobacz Oddzielanie schematu użytkownika.

• Należy unikać zawijania funkcje wokół kolumn określona w WHERE i klauzule sprzężenia.Wykonanie tej czynności powoduje spoza firmy Deterministic Networks kolumn i zapobiega używaniu indeksów procesor kwerend.

• Należy unikać używania funkcji wartość skalarna w instrukcji SELECT, które zwracają wielu wierszy danych.Ponieważfunkcja wartość skalarnamuszą być stosowane do każdego wiersza, Wynikowe zachowanie jest podobne do przetwarzania na podstawie wiersza i obniża wydajność.

• Unikaj stosowania wybierz *.Zamiast tego należy określić nazwy wymaganej kolumna .Może to uniemożliwić niektóre Aparat baz danych błędy, które należy zatrzymać wykonywanie procedury.Na przykład wybierz * instrukcja , która zwraca dane z 12 kolumna tabela , a następnie wstawia dane do 12 kolumna tymczasowej tabela zostanie wykonana pomyślnie, aż liczba lub zmieniła się kolejność kolumn w jednej tabela .

• Należy unikać przetwarzania lub zwracanie zbyt dużej ilości danych.Zawęź wyniki jak najwcześniej w kodzie procedury tak, aby późniejsze operacje wykonywane poprzez procedury są wykonywane za pomocą najmniejszą zestaw danych możliwych.Wysyłanie do klient podstawowych danych aplikacji jest również bardziej efektywne niż wysyłanie dodatkowych danych w sieci i wymuszanie aplikacji klient działa poprzez niepotrzebnie duży zestaw wyników.

• Korzystania z transakcji jawnej przy użyciu transakcji POCZĄTKOWY i końcowy, a transakcje możliwie jak najkrótszy.Transakcje dłuższy oznacza dłuższy blokowanie rekordów i większy potencjał dla deadlocking.Aby uzyskać więcej informacji, zobacz Locking and Versioning wiersza, Zablokuj zgodności (aparat bazy danych) lub Poziom izolacji w aparacie bazy danych.

• Należy unikać używania symboli wieloznacznych jako znak wiodący w PODOBNYCH klauzula, na przykład, takich jak % %.Ponieważ pierwszy znak jest inne niż firmy Deterministic Networks, procesor kwerend nie może używać dostępnych indeksów.Użyj %.

• Użycie Transact-SQL TRY…POŁÓW funkcji obsługa błędów wewnątrz procedury.TRY…POŁÓW można upakować cały blok z Transact-SQL instrukcji.Tworzy to nie tylko mniejsze obciążenie, ale także raportowanie błędów dokładniejsze znacznie mniej programowania.Aby uzyskać więcej informacji, zobacz Za pomocą SPRÓBOWAĆ...POŁOWU w języku Transact-SQL.

• Za pomocą słowa kluczowego DOMYŚLNYCH na wszystkie kolumny tabela , do których odwołuje instrukcji ALTER TABLE lub CREATE TABLE Transact-SQL instrukcji w treści procedury.Uniemożliwi to przekazanie wartości NULL do kolumny nie dopuszczającej wartości null .

• Należy użyć wartości NULL lub NOT NULL dla każdej kolumna w tabelatymczasowej.Sposób sterowania Opcje ANSI_DFLT_ON i ANSI_DFLT_OFF Aparat baz danych przypisuje wartości NULL ani nie atrybutów wartości NULL do kolumny te atrybuty nie są określone w instrukcjaALTER TABLE lub CREATE TABLE.Jeśli połączenie wykonuje procedurę z różnymi ustawieniami opcji niż połączenia, który utworzył procedurę, kolumny tabela utworzone dla drugiego połączenia ma inną opcje dopuszczania wartości null i wykazują różne zachowanie.Jeśli wartość NULL lub NOT NULL jest jawny dla każdej kolumnatabel tymczasowych są tworzone przy użyciu tej samej opcje dopuszczania wartości null dla wszystkich połączeń tego wykonać procedurę.

• Użyć instrukcji modyfikacji, które konwersja wartości null i zawiera logikę, która eliminuje wiersze z wartościami null z kwerend.Należy pamiętać, że w Transact-SQL, NULL nie jest pusty lub "nothing" wartość.Jest symbolem zastępczym Nieznana wartość i może spowodować nieoczekiwane zachowanie, szczególnie w przypadku kwerend dla wyniku Ustawia lub przy użyciu funkcji AGREGUJĄCYCH.Aby uzyskać więcej informacji, zobacz Warunki wyszukiwania porównanie NULL i Wartości null.

• Użyj operator UNION ALL zamiast Unii lub operatory OR, chyba że istnieją szczególne potrzeby różnych wartości.operator UNION ALL zużywanych przetwarzania ponieważ duplikatów nie są filtrowane z zestaw wyników.

Uwagi ogólne

Nie ma żadnych wstępnie zdefiniowanych maksymalny rozmiar procedurę.

Gdy procedura jest wykonywana pierwszy czas, jest kompilowana do ustalenia planu optymalny dostęp do pobierania danych.Następne wykonania procedury może ponownie użyć planu już generowane, jeśli nadal pozostaje w pamięci podręcznej planu Aparat baz danych.Aby uzyskać więcej informacji, zobacz Wykonanie planu buforowania i ponownego użycia lub Procedura przechowywana i wykonywania wyzwalacza.

Jeden lub więcej procedur można wykonać automatycznie po SQL Server uruchomieniu.Procedury muszą być utworzone przez administrator systemu w wzorca bazy danych i wykonywane w sysadmin stała rola serwera w tle.Procedury nie może mieć żadnych parametrów wejściowych lub wyjściowych.Aby uzyskać więcej informacji, zobacz Wykonywanie przechowywanych procedur (aparat bazy danych).

Można zagnieżdżać procedur; maksymalnie 32 poziomy.Oznacza to, że w jednej procedurze można wywołać inną.poziom zagnieżdżenia jest zwiększany przy uruchamianiu wywołana procedury działa i jest zmniejszana po zakończeniu wykonywania wywołana procedury.Aby uzyskać więcej informacji, zobacz Zaprojektowanie procedury przechowywane (aparat bazy danych).

Zmienne wewnątrz procedury mogą być zdefiniowane przez użytkownika lub zmienne systemowe, takie jak @@ SPID.

Współdziałanie

Aparat baz danych Zapisuje ustawienia zestawu QUOTED_IDENTIFIER i USTAWIĆ ANSI_NULLS, gdy Transact-SQL procedura jest tworzone lub modyfikowane.Oryginalne ustawienia te są używane podczas wykonywania procedury.Dlatego wszelkie ustawieniasesja klientdla zestawu QUOTED_IDENTIFIER i USTAWIĆ ANSI_NULLS są ignorowane podczas uruchamiania procedury.

Inne opcje, takie jak zestaw ARITHABORT zestawu, zestaw ANSI_WARNINGS lub USTAWIĆ ANSI_PADDINGS są włączane nie są zapisywane po utworzeniu lub zmodyfikowaniu procedurę.Jeśli logiki procedura zależy od określonego ustawienia, należy dołączyć zestaw instrukcja na początku procedury, aby zagwarantować odpowiednie ustawienie.Gdy zestaw instrukcja jest wykonana z procedury, ustawienie obowiązuje tylko do momentu zakończeniu procedury.Ustawienie jest następnie przywrócone do wartości procedury miał, gdy została wywołana.Umożliwia to klientów indywidualnych, aby zestaw opcje, które mają być bez wpływu na logiki procedury.

Każdy zestaw instrukcja można określić wewnątrz procedury, z wyjątkiem zestawu SHOWPLAN_TEXT i ustaw SHOWPLAN_ALL.Muszą to być jedynymi instrukcjami partia.Wybranego zestawu opcji obowiązuje podczas wykonywania procedury, a następnie przywraca poprzednie ustawienia.Aby uzyskać więcej informacji na temat zestawu opcji, zobacz Ustawianie opcji.

Ograniczenia i ograniczenia

instrukcja CREATE PROCEDURE nie można łączyć z innymi Transact-SQL instrukcji w jednej partia.

Poniższe instrukcje nie używana w dowolnym miejscu w treści procedura składowana.

Procedurę można odwoływać się do tabel, które jeszcze nie istnieje.W czastworzenia wykonywane jest tylko sprawdzanie składni.Procedura nie jest skompilowany, dopóki nie jest wykonywana pierwszy czas.Tylko podczas kompilacji są wszystkie obiekty, do których odwołuje się procedura rozwiązany.Dlatego też poprawne syntaktycznie procedury, która odwołuje się do tabel, które nie istnieją można tworzyć pomyślnie; Jednakże procedura zakończy się niepowodzeniem w czas wykonywania, nie istnieją tabele, do którego istnieje odwołanie.Aby uzyskać więcej informacji, zobacz Odroczone rozpoznawanie nazw i kompilacji.

Nie można określić nazwę funkcja , jako wartość domyślną parametru lub wartość przekazanego do parametru podczas wykonywania procedury.

Wewnątrz procedury, nazwy używane do wszystkich instrukcji języka definicji danych (DDL), takie jak CREATE, ALTER, lub UPUSZCZANIA sprawozdania, DBCC instrukcje, wykonywanie i dynamicznych obiektów Transact-SQL musi być kwalifikowany sprawozdania o nazwie schematu obiektu, jeśli użytkownicy inni niż właściciel procedury należy użyć procedury.Aby uzyskać więcej informacji, zobacz Zaprojektowanie procedury przechowywane (aparat bazy danych).

Dla Aparat baz danych , aby w trakcie jest nadmiernie obciążony w poprawnej metoda .NET Framework metoda określonej w nazw zewnętrznych klauzula musi mieć następujące cechy:

• Można zadeklarować jako statyczna metoda.

• Otrzymują tę samą liczbę parametrów, jak liczba parametrów procedury.

• Typy parametrów, które są zgodne z typami danych odpowiednich parametrów za pomocą SQL Server procedury.Aby uzyskać informacje dotyczące dopasowywania SQL Server typy danych do .NET Framework typów danych, zobacz Mapowanie danych parametru CLR.

Metadane

Aby wyświetlić definicję Transact-SQL procedury, użyj sys.sql_modules katalogu widoku w bazie danych, w której istnieje procedura.Tekst procedury utworzone za pomocą opcji szyfrowania nie można przeglądać za pomocą sys.sql_modules katalogu widoku.

Na przykład:

USE AdventureWorks2008R2;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P'; 

W przypadku raportu o obiektach odwołuje się procedura kwerendy sys.sql_expression_dependencies katalogu widok lub użyj sys.dm_sql_referenced_entities i sys.dm_sql_referencing_entities.

Aby wyświetlić informacje dotyczące procedur CLR, użyj sys.assembly_modules katalogu widoku w bazie danych, w której istnieje procedura.

Aby wyświetlić informacje na temat parametrów, które są zdefiniowane w procedurze, użyj sys.parameters katalogu widoku w bazie danych, w której istnieje procedura.

Aby oszacować rozmiar skompilowanego procedurę, należy użyć następujących liczników Monitora wydajności.

* Te liczniki są dostępne dla różnych kategorii obiektów pamięci podręcznej, w tym ad hoc, Transact-SQL, przygotowane Transact-SQL, procedur, wyzwalacze i tak dalej.Aby uzyskać więcej informacji, zobacz SQL Server Plan pamięci podręcznej obiektu.

Zabezpieczenia

Uprawnienia

Przykłady

A.Tworzenie prostą procedurę

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

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

B.Tworzenie prostą procedurę z parametrami

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

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

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

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.Zwracanie więcej niż jeden zestaw wyników

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(BusinessEntityID) FROM Person.Person
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

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

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

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)))+'.'

F.Za pomocą opcji WITH RECOMPILE

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v 
    JOIN Purchasing.ProductVendor AS pv 
      ON v.BusinessEntityID = pv.BusinessEntityID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G.Za pomocą opcji WITH ENCRYPTION

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

EXEC sp_helptext 'HumanResources.uspEncryptThis';

USE AdventureWorks2008R2;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

H.Za pomocą rozpoznawania nazw odroczonego

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

USE AdventureWorks2008R2;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

I.Na podstawie EXECUTE jako klauzula

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name', 
      v.CreditRating AS 'Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

J.Tworzenie procedura składowana CLR

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID int,
    @CurrentDirectory nvarchar(1024),
    @FileName nvarchar(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

K.Za pomocą parametru WYJŚCIOWEGO kursor

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

USE AdventureWorks2008R2;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

Zobacz także

Zadania

Odwołanie

Koncepcje

Inne zasoby