PROCEDURA CREATE (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punkt końcowy analizy SQL w usłudze Microsoft FabricHurtownia danych w usłudze Microsoft FabricBaza danych SQL w usłudze Microsoft Fabric

Tworzy procedurę Transact-SQL lub common language runtime (CLR) w SQL Server, Azure SQL Database, SQL Database w Microsoft Fabric oraz Analytics Platform System (PDW). Procedury składowane są podobne do procedur w innych językach programowania, w których mogą:

• Zaakceptuj parametry wejściowe i zwróć wiele wartości w postaci parametrów wyjściowych do procedury wywołującej lub partii.
• Zawierają instrukcje programowania, które wykonują operacje w bazie danych, w tym wywoływanie innych procedur.
• Zwróć wartość stanu do procedury wywołującej lub partii, aby wskazać powodzenie lub niepowodzenie (i przyczynę niepowodzenia).

Użyj tej instrukcji, aby utworzyć trwałą procedurę w bieżącej bazie danych lub procedurę tymczasową w tempdb bazie danych.

Uwaga / Notatka

Integracja środowiska .NET Framework CLR z programem SQL Server została omówiona w tym temacie. Integracja z CLR nie dotyczy bazy danych Azure SQL ani bazy danych SQL w Microsoft Fabric.

Przejdź do prostych przykładów , aby pominąć szczegóły składni i przejść do szybkiego przykładu podstawowej procedury składowanej.

Transact-SQL konwencje składni

Składnia

Transact-SQL składnia procedur przechowywanych w SQL Server, Azure SQL Database, SQL database w Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

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

Transact-SQL składnia procedur składowanych CLR:

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

Transact-SQL składni dla natywnie skompilowanych procedur składowanych:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
        [ OUT | OUTPUT ] [READONLY]
    ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
  BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]

<set_option> ::=
    LANGUAGE = [ N ] 'language'
  | TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
  | [ DATEFIRST = number ]
  | [ DATEFORMAT = format ]
  | [ DELAYED_DURABILITY = { OFF | ON } ]

Transact-SQL składni procedur składowanych w usługach Azure Synapse Analytics i Parallel Data Warehouse:

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Transact-SQL składni procedur składowanych w usłudze Microsoft Fabric:

CREATE [ OR ALTER ] { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

Arguments

LUB ALTER

Dotyczy do: bazy danych Azure SQL, bazy danych SQL w Microsoft Fabric, SQL Server (począwszy od SQL Server 2016 (13.x) SP1).

Zmienia procedurę, jeśli już istnieje.

schema_name

Nazwa schematu, do którego należy procedura. Procedury są powiązane ze schematem. Jeśli nazwa schematu nie zostanie określona podczas tworzenia procedury, zostanie automatycznie przypisany domyślny schemat użytkownika tworzącego procedurę.

procedure_name

Nazwa procedury. Nazwy procedur muszą być zgodne z regułami identyfikatorów i muszą być unikatowe w schemacie.

Ostrzeżenie

Unikaj używania prefiksu sp_ podczas nazywania procedur. Ten prefiks jest używany przez program SQL Server do wyznaczania procedur systemowych. Użycie prefiksu może spowodować przerwanie kodu aplikacji, jeśli istnieje procedura systemowa o tej samej nazwie.

Lokalne lub globalne procedury tymczasowe można utworzyć przy użyciu jednego znaku numeru (#) przed procedure_name (#procedure_name) dla lokalnych procedur tymczasowych i dwóch znaków liczbowych dla globalnych procedur tymczasowych (#procedure_name). Lokalna procedura tymczasowa jest widoczna tylko dla połączenia, które je utworzyło i jest przerywana po zamknięciu tego połączenia. Globalna procedura tymczasowa jest dostępna dla wszystkich połączeń i jest porzucana na końcu ostatniej sesji przy użyciu procedury. Nie można określić nazw tymczasowych dla procedur CLR.

Pełna nazwa procedury lub globalnej procedury tymczasowej, w tym ##, nie może przekraczać 128 znaków. Pełna nazwa lokalnej procedury tymczasowej, w tym #, nie może przekraczać 116 znaków.

; numer

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Opcjonalna liczba całkowita używana do grupowania procedur o tej samej nazwie. Te pogrupowane procedury można porzucić przy użyciu jednej instrukcji DROP PROCEDURE.

Uwaga / Notatka

Ta funkcja zostanie usunięta w przyszłej wersji programu SQL Server. Unikaj używania tej funkcji w nowych pracach programistycznych i zaplanuj modyfikowanie aplikacji, które obecnie korzystają z tej funkcji.

Procedury numerowane nie mogą używać typów zdefiniowanych przez użytkownika xml ani CLR i nie można ich używać w przewodniku planu.

@ parameter_name

Parametr zadeklarowany w procedurze. Określ nazwę parametru przy użyciu znaku (@) jako pierwszego znaku. Nazwa parametru musi być zgodna z regułami identyfikatorów. Parametry są lokalne dla procedury; te same nazwy parametrów mogą być używane w innych procedurach.

Można zadeklarować co najmniej jeden parametr; wartość maksymalna to 2100. Wartość każdego zadeklarowanego parametru musi być podana przez użytkownika, gdy procedura jest wywoływana, chyba że zdefiniowana jest wartość domyślna parametru lub wartość jest ustawiona na wartość innego parametru. Jeśli procedura zawiera parametry z wartością tabeli, a w wywołaniu brakuje parametru, zostanie przekazana pusta tabela. Parametry mogą mieć miejsce tylko wyrażeń stałych; nie można ich używać zamiast nazw tabel, nazw kolumn ani nazw innych obiektów bazy danych. Aby uzyskać więcej informacji, zobacz EXECUTE (Transact-SQL).

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

[ type_schema_name. ] data_type

Typ danych parametru i schemat, do którego należy typ danych.

Wytyczne dotyczące procedur Transact-SQL:

• Wszystkie typy danych Transact-SQL mogą być używane jako parametry.
• Możesz użyć typu tabeli zdefiniowanej przez użytkownika, aby utworzyć parametry wartości tabeli. Parametry wartości tabeli mogą być tylko parametrami WEJŚCIOWYmi i muszą być dołączone przez słowo kluczowe READONLY. Aby uzyskać więcej informacji, zobacz Use Table-Valued Parameters (Aparat bazy danych)
• Typy danych kursora mogą być tylko parametrami WYJŚCIOWYmi i muszą być dołączone przez słowo kluczowe VARYING.

Wytyczne dotyczące procedur CLR:

• Wszystkie natywne typy danych programu SQL Server, które mają odpowiednik w kodzie zarządzanym, mogą być używane jako parametry. Aby uzyskać więcej informacji na temat korespondencji między typami CLR i typami danych systemowych programu SQL Server, zobacz Mapowanie danych parametrów CLR. Aby uzyskać więcej informacji na temat typów danych systemowych programu SQL Server i ich składni, zobacz Typy danych (Transact-SQL).

• Nie można używać typów danych wartości tabeli lub kursora jako parametrów.

• Jeśli typ danych parametru jest typem zdefiniowanym przez użytkownika CLR, musisz mieć uprawnienie EXECUTE dla typu.

RÓŻNYCH

Określa zestaw wyników obsługiwany jako parametr wyjściowy. Ten parametr jest dynamicznie konstruowany przez procedurę, a jego zawartość może się różnić. Dotyczy tylko parametrów kursora . Ta opcja nie jest prawidłowa w przypadku procedur CLR.

domyślne

Wartość domyślna parametru. Jeśli wartość domyślna jest zdefiniowana dla parametru, można wykonać procedurę bez określania wartości dla tego parametru. Wartość domyślna musi być stałą lub może mieć wartość NULL. Stała wartość może być w postaci symbolu wieloznakowego, dzięki czemu można użyć słowa kluczowego LIKE podczas przekazywania parametru do procedury.

Wartości domyślne są rejestrowane tylko w kolumnie sys.parameters.default dla procedur CLR. Ta kolumna ma wartość NULL dla parametrów procedury Transact-SQL.

OUT | WYJŚCIE

Wskazuje, że parametr jest parametrem wyjściowym. Użyj parametrów OUTPUT, aby zwrócić wartości do obiektu wywołującego procedurę. parametrów tekstu, ntekstu i obrazu nie można używać jako parametrów WYJŚCIOWYch, chyba że procedura jest procedurą CLR. Parametr wyjściowy może być symbolem zastępczym kursora, chyba że procedura jest procedurą CLR. Nie można określić typu danych wartości tabeli jako parametru WYJŚCIOWEgo procedury.

READONLY

Wskazuje, że nie można zaktualizować ani zmodyfikować parametru w treści procedury. Jeśli typ parametru jest typem wartości tabeli, należy określić wartość READONLY.

RECOMPILE

Wskazuje, że aparat bazy danych nie buforuje planu zapytania dla tej procedury, zmuszając go do skompilowania za każdym razem, gdy jest wykonywany. Aby uzyskać więcej informacji na temat przyczyn wymuszania ponownego kompilowania, zobacz Recompile a Stored Procedure (Ponowne kompilowanie procedury składowanej). Tej opcji nie można użyć, gdy dla replikacji określono lub dla procedur CLR.

Aby poinstruować aparat bazy danych, aby odrzucić plany zapytań dla poszczególnych zapytań wewnątrz procedury, użyj wskazówki zapytania RECOMPILE w definicji zapytania. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań (Transact-SQL).

SZYFROWANIE

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Wskazuje, że program SQL Server konwertuje oryginalny tekst instrukcji CREATE PROCEDURE na zaciemniony format. Dane wyjściowe zaciemnienia nie są bezpośrednio widoczne w żadnym z widoków wykazu w programie SQL Server. Użytkownicy, którzy nie mają dostępu do systemowych tabel lub plików bazy danych, nie mogą pobrać zaciemnionego tekstu. Tekst jest jednak dostępny dla uprzywilejowanych użytkowników, którzy mogą uzyskiwać dostęp do tabel systemowych za pośrednictwem portu DAC lub bezpośrednio uzyskiwać dostęp do plików bazy danych. Ponadto użytkownicy, którzy mogą dołączyć debuger do procesu serwera, mogą pobrać procedurę odszyfrowywania z pamięci w czasie wykonywania. Aby uzyskać więcej informacji na temat uzyskiwania dostępu do metadanych systemu, zobacz Konfiguracja widoczności metadanych.

Ta opcja nie jest prawidłowa w przypadku procedur CLR.

Procedury utworzone za pomocą tej opcji nie mogą być publikowane w ramach replikacji programu SQL Server.

KLAUZULA EXECUTE AS

Określa kontekst zabezpieczeń, w którym ma zostać wykonana procedura.

W przypadku natywnie skompilowanych procedur składowanych nie ma żadnych ograniczeń dotyczących klauzuli EXECUTE AS . W programach SQL Server 2014 (12.x) i starszych wersjach SELFklauzule , OWNERi "user_name" są obsługiwane z natywnie skompilowanymi procedurami składowanymi.

Aby uzyskać więcej informacji, zobacz EXECUTE AS Clause (Transact-SQL).

REPLIKACJA

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Określa, że procedura jest tworzona na potrzeby replikacji. W związku z tym nie można go wykonać na subskrybenta. Procedura utworzona za pomocą opcji REPLIKACJA FOR jest używana jako filtr procedury i jest wykonywana tylko podczas replikacji. Nie można zadeklarować parametrów, jeśli określono dla replikacji. W przypadku procedur CLR nie można określić replikacji. Opcja RECOMPILE jest ignorowana dla procedur utworzonych za pomocą funkcji REPLIKACJA.

Procedura FOR REPLICATION ma typ obiektu RF w sys.objects i sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ... n ] [ END ] }

Co najmniej jedno Transact-SQL instrukcje składające się z treści procedury. Możesz użyć opcjonalnych słów kluczowych BEGIN i END, aby ująć instrukcje. Aby uzyskać informacje, zobacz sekcje Najlepsze rozwiązania, Uwagi ogólne i Ograniczenia, które są opisane poniżej.

ASSEMBLY_NAME NAZWA ZEWNĘTRZNA. class_name. method_name

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Określa metodę zestawu .NET Framework dla procedury CLR do odwołania. class_name musi być prawidłowym identyfikatorem programu SQL Server i musi istnieć jako klasa w zestawie. Jeśli klasa ma kwalifikowaną nazwę przestrzeni nazw, która używa kropki (.) do oddzielania części przestrzeni nazw, nazwa klasy musi być rozdzielana nawiasami kwadratowymi () lub cudzysłowami ([]""). Określona metoda musi być statyczną metodą klasy.

Domyślnie program SQL Server nie może wykonać kodu CLR. Można tworzyć, modyfikować i usuwać obiekty bazy danych odwołujące się do modułów środowiska uruchomieniowego języka wspólnego; nie można jednak wykonać tych odwołań w programie SQL Server, dopóki nie włączysz opcji clr enabled. Aby włączyć tę opcję, użyj sp_configure.

Uwaga / Notatka

Procedury CLR nie są obsługiwane w zawartej bazie danych.

NIEPODZIELNE Z

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Wskazuje niepodzielne wykonanie procedury składowanej. Zmiany są zatwierdzane lub wszystkie zmiany wycofane przez zgłoszenie wyjątku. Blok ATOMIC WITH jest wymagany dla natywnie skompilowanych procedur składowanych.

Jeśli procedura RETURNs (jawnie za pośrednictwem instrukcji RETURN lub niejawnie przez ukończenie wykonywania), praca wykonywana przez procedurę jest zatwierdzana. Jeśli procedura THROWs, praca wykonywana przez procedurę zostanie wycofana.

XACT_ABORT jest domyślnie włączony wewnątrz bloku niepodzielnego i nie można go zmienić. XACT_ABORT określa, czy program SQL Server automatycznie wycofa bieżącą transakcję, gdy instrukcja Transact-SQL zgłasza błąd czasu wykonywania.

Następujące opcje SET są zawsze włączone w bloku ATOMIC i nie można ich zmienić.

• CONCAT_NULL_YIELDS_NULL (łączenie z wartością NULL daje NULL)
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS

Nie można zmienić opcji SET wewnątrz bloków ATOMIC. Opcje SET w sesji użytkownika nie są używane w zakresie natywnie skompilowanych procedur składowanych. Te opcje są stałe w czasie kompilacji.

Operacje BEGIN, ROLLBACK i COMMIT nie mogą być używane wewnątrz bloku niepodzielnego.

Istnieje jeden blok ATOMIC na natywnie skompilowaną procedurę składowaną w zewnętrznym zakresie procedury. Nie można zagnieżdżać bloków. Aby uzyskać więcej informacji na temat bloków niepodzielnych, zobacz Natywnie skompilowane procedury składowane.

NULL | NIE NULL

Określa, czy wartości null są dozwolone w parametrze. Wartość NULL jest wartością domyślną.

NATIVE_COMPILATION

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Wskazuje, że procedura jest natywnie skompilowana. NATIVE_COMPILATION, SCHEMABINDING i EXECUTE AS można określić w dowolnej kolejności. Aby uzyskać więcej informacji, zobacz Natywnie skompilowane procedury składowane.

POWIĄZANIE SCHEMATU

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Zapewnia, że tabele, do których odwołuje się procedura, nie mogą zostać usunięte ani zmienione. SCHEMATBINDING jest wymagany w natywnie skompilowanych procedurach składowanych. (Aby uzyskać więcej informacji, zobacz Natywnie skompilowane procedury składowane). Ograniczenia SCHEMABINDING są takie same jak w przypadku funkcji zdefiniowanych przez użytkownika. Aby uzyskać więcej informacji, zobacz sekcję SCHEMABINDING w temacie CREATE FUNCTION (Transact-SQL).

JĘZYK = [N] 'język'

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Odpowiednik opcji sesji SET LANGUAGE (Transact-SQL). Język = [N] 'language' jest wymagany.

POZIOM IZOLACJI TRANSAKCJI

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Wymagane dla natywnie skompilowanych procedur składowanych. Określa poziom izolacji transakcji dla procedury składowanej. Opcje są następujące:

Aby uzyskać więcej informacji na temat tych opcji, zobacz USTAWIANIE POZIOMU IZOLACJI TRANSAKCJI (Transact-SQL).

POWTARZALNY ODCZYT

Określa, że instrukcje nie mogą odczytywać danych, które zostały zmodyfikowane, ale nie zostały jeszcze zatwierdzone przez inne transakcje. Jeśli inna transakcja modyfikuje dane odczytane przez bieżącą transakcję, bieżąca transakcja zakończy się niepowodzeniem.

SERIALIZACJI

Określa następujące elementy:

• Instrukcje nie mogą odczytywać danych, które zostały zmodyfikowane, ale nie zostały jeszcze zatwierdzone przez inne transakcje.
• Jeśli inna transakcja modyfikuje dane odczytane przez bieżącą transakcję, bieżąca transakcja zakończy się niepowodzeniem.
• Jeśli inna transakcja wstawia nowe wiersze z wartościami klucza, które spadną w zakresie kluczy odczytanych przez dowolne instrukcje w bieżącej transakcji, bieżąca transakcja zakończy się niepowodzeniem.

MIGAWKA

Określa, że dane odczytywane przez dowolną instrukcję w transakcji to transakcyjnie spójna wersja danych, które istniały na początku transakcji.

DATEFIRST = liczba

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Określa pierwszy dzień tygodnia na liczbę z zakresu od 1 do 7. Element DATEFIRST jest opcjonalny. Jeśli nie zostanie określony, ustawienie zostanie wywnioskowane z określonego języka.

Aby uzyskać więcej informacji, zobacz SET DATEFIRST (Transact-SQL).

DATEFORMAT = format

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Określa kolejność części daty miesiąca, dnia i roku do interpretowania ciągów znaków date, smalldatetime, datetime, datetime2 i datetimeoffset. Format DATEFORMAT jest opcjonalny. Jeśli nie zostanie określony, ustawienie zostanie wywnioskowane z określonego języka.

Aby uzyskać więcej informacji, zobacz SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | WŁ. }

Dotyczy do: SQL Server 2014 (12.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

Zatwierdzenia transakcji programu SQL Server mogą być w pełni trwałe, domyślne lub opóźnione.

Aby uzyskać więcej informacji, zobacz Control Transaction Durability (Kontrolowanie trwałości transakcji).

Proste przykłady

Aby ułatwić rozpoczęcie pracy, poniżej przedstawiono dwa szybkie przykłady: SELECT DB_NAME() AS ThisDB; zwraca nazwę bieżącej bazy danych. Taką instrukcję można opakowować w procedurze składowanej, na przykład:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Wywołaj procedurę magazynu za pomocą instrukcji: EXEC What_DB_is_this;

Nieco bardziej złożone jest podanie parametru wejściowego w celu zwiększenia elastyczności procedury. Przykład:

CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;

Podaj numer identyfikatora bazy danych podczas wywoływania procedury. Na przykład EXEC What_DB_is_that 2; zwraca tempdb.

Zobacz Przykłady na końcu tego artykułu, aby uzyskać więcej przykładów.

Najlepsze rozwiązania

Chociaż nie jest to wyczerpująca lista najlepszych rozwiązań, te sugestie mogą poprawić wydajność procedury.

• Użyj instrukcji SET NOCOUNT ON jako pierwszej instrukcji w treści procedury. Oznacza to, że umieść go tuż po słowie kluczowym AS. Spowoduje to wyłączenie komunikatów wysyłanych przez program SQL Server z powrotem do klienta po wykonaniu instrukcji SELECT, INSERT, UPDATE, MERGE i DELETE. Dzięki temu dane wyjściowe są generowane co najmniej w celu uzyskania przejrzystości. Nie ma jednak mierzalnych korzyści z wydajności na dzisiejszym sprzęcie. Aby uzyskać informacje, zobacz SET NOCOUNT (Transact-SQL).
• Użyj nazw schematów podczas tworzenia lub odwoływania się do obiektów bazy danych w procedurze. Rozpoznawanie nazw obiektów przez aparat bazy danych zajmuje mniej czasu przetwarzania, jeśli nie musi przeszukiwać wielu schematów. Zapobiega to również problemom z uprawnieniami i dostępem spowodowanym domyślnym schematem użytkownika podczas tworzenia obiektów bez określania schematu.
• Unikaj zawijania funkcji wokół kolumn określonych w klauzulach WHERE i JOIN. W ten sposób kolumny nie są deterministyczne i uniemożliwia procesorowi zapytań korzystanie z indeksów.
• Unikaj używania funkcji skalarnych w instrukcjach SELECT, które zwracają wiele wierszy danych. Ponieważ funkcja skalarna musi być stosowana do każdego wiersza, wynikowe zachowanie jest podobne do przetwarzania opartego na wierszach i obniża wydajność.
• Unikaj używania elementu SELECT *. Zamiast tego określ wymagane nazwy kolumn. Może to zapobiec niektórym błędom aparatu bazy danych, które zatrzymują wykonywanie procedury. Na przykład instrukcja zwracająca SELECT * dane z tabeli 12 kolumn, a następnie wstawia te dane do tabeli tymczasowej 12 kolumn, dopóki liczba lub kolejność kolumn w tabeli nie zostanie zmieniona.
• Unikaj przetwarzania lub zwracania zbyt dużej ilości danych. Zawęź wyniki tak szybko, jak to możliwe, w kodzie procedury, aby wszystkie kolejne operacje wykonywane przez procedurę były wykonywane przy użyciu najmniejszego zestawu danych. Wysyłaj tylko podstawowe dane do aplikacji klienckiej. Jest to bardziej wydajne niż wysyłanie dodatkowych danych w sieci i zmuszanie aplikacji klienckiej do pracy przez niepotrzebnie duże zestawy wyników.
• Użyj jawnych transakcji przy użyciu funkcji BEGIN/COMMIT TRANSACTION i zachowaj transakcje tak krótko, jak to możliwe. Dłuższe transakcje oznaczają dłuższe blokowanie rekordów i większy potencjał zakleszczenia.
• Użyj Transact-SQL TRY... Funkcja CATCH do obsługi błędów wewnątrz procedury. PRÓBOWAĆ... CATCH może hermetyzować cały blok instrukcji Transact-SQL. Nie tylko powoduje to zmniejszenie obciążenia związanego z wydajnością, ale także sprawia, że raportowanie błędów jest bardziej dokładne z znacznie mniejszym programowaniem.
• Użyj słowa kluczowego DEFAULT we wszystkich kolumnach tabeli, do których odwołują się instrukcje CREATE TABLE lub ALTER TABLE Transact-SQL w treści procedury. Zapobiega to przekazywaniu wartości NULL do kolumn, które nie zezwalają na wartości null.
• Użyj wartości NULL lub NOT NULL dla każdej kolumny w tabeli tymczasowej. Opcje ANSI_DFLT_ON i ANSI_DFLT_OFF kontrolują sposób przypisywania atrybutów NULL lub NOT NULL do kolumn, gdy te atrybuty nie są określone w instrukcji CREATE TABLE lub ALTER TABLE. Jeśli połączenie wykonuje procedurę z różnymi ustawieniami dla tych opcji niż połączenie, które utworzyło procedurę, kolumny tabeli utworzonej dla drugiego połączenia mogą mieć inną wartość null i wykazują inne zachowanie. Jeśli dla każdej kolumny jawnie określono wartość NULL lub NOT NULL, tabele tymczasowe są tworzone przy użyciu tej samej wartości null dla wszystkich połączeń wykonujących procedurę.
• Użyj instrukcji modyfikacji, które konwertują wartości null i zawierają logikę, która eliminuje wiersze z wartościami null z zapytań. Należy pamiętać, że w języku Transact-SQL wartość NULL nie jest pusta ani "nic". Jest to symbol zastępczy nieznanej wartości i może powodować nieoczekiwane zachowanie, zwłaszcza w przypadku wykonywania zapytań dotyczących zestawów wyników lub używania funkcji AGGREGATE.
• Użyj operatora UNION ALL zamiast operatorów UNION lub OR, chyba że istnieje określona potrzeba dla odrębnych wartości. Operator UNION ALL wymaga mniejszego obciążenia związanego z przetwarzaniem, ponieważ duplikaty nie są filtrowane z zestawu wyników.

Uwagi

Nie ma wstępnie zdefiniowanego maksymalnego rozmiaru procedury.

Zmienne określone w procedurze mogą być zdefiniowane przez użytkownika lub zmienne systemowe, takie jak @@SPID.

Gdy procedura jest wykonywana po raz pierwszy, jest kompilowana w celu określenia optymalnego planu dostępu w celu pobrania danych. Kolejne wykonania procedury mogą używać ponownie wygenerowanego planu, jeśli nadal pozostaje w pamięci podręcznej planu aparatu bazy danych.

Co najmniej jedna procedura może być wykonywana automatycznie po uruchomieniu programu SQL Server. Procedury należy utworzyć przez administratora systemu w master bazie danych i wykonać w ramach stałej roli serwera sysadmin jako proces w tle. Procedury nie mogą mieć żadnych parametrów wejściowych ani wyjściowych. Aby uzyskać więcej informacji, zobacz Wykonywanie procedury składowanej.

Procedury są zagnieżdżone, gdy jedna procedura wywołuje inny lub wykonuje kod zarządzany, odwołując się do procedury CLR, typu lub agregacji. Procedury i odwołania kodu zarządzanego można zagnieżdżać do 32 poziomów. Poziom zagnieżdżania zwiększa się o jeden, gdy wywoływana procedura lub odwołanie kodu zarządzanego rozpoczyna wykonywanie i zmniejsza się o jeden, gdy wywołana procedura lub odwołanie kodu zarządzanego zakończy wykonywanie. Metody wywoływane z poziomu kodu zarządzanego nie są liczone względem limitu poziomu zagnieżdżania. Jednak gdy procedura składowana CLR wykonuje operacje dostępu do danych za pośrednictwem dostawcy zarządzanego programu SQL Server, dodatkowy poziom zagnieżdżania jest dodawany w ramach przejścia z kodu zarządzanego do programu SQL.

Próba przekroczenia maksymalnego poziomu zagnieżdżania powoduje niepowodzenie całego łańcucha wywołań. Za pomocą funkcji @@NESTLEVEL można zwrócić poziom zagnieżdżania bieżącej procedury składowanej.

Współdziałanie

Aparat bazy danych zapisuje ustawienia QUOTED_IDENTIFIER SET i SET ANSI_NULLS podczas tworzenia lub modyfikowania procedury Transact-SQL. Te oryginalne ustawienia są używane podczas wykonywania procedury. W związku z tym wszystkie ustawienia sesji klienta dla QUOTED_IDENTIFIER SET i SET ANSI_NULLS są ignorowane podczas uruchamiania procedury.

Inne opcje ZESTAWU, takie jak SET ARITHABORT, SET ANSI_WARNINGS lub SET ANSI_PADDINGS nie są zapisywane podczas tworzenia lub modyfikowania procedury. Jeśli logika procedury zależy od określonego ustawienia, dołącz instrukcję SET na początku procedury w celu zagwarantowania odpowiedniego ustawienia. Po wykonaniu instrukcji SET z procedury ustawienie pozostaje w mocy tylko do momentu zakończenia działania procedury. Ustawienie zostanie następnie przywrócone do wartości, którą procedura miała po wywołaniu. Umożliwia to poszczególnym klientom ustawianie żądanych opcji bez wpływu na logikę procedury.

Każdą instrukcję SET można określić wewnątrz procedury, z wyjątkiem SET SHOWPLAN_TEXT i SET SHOWPLAN_ALL. Muszą to być jedyne instrukcje w partii. Wybrana opcja SET pozostaje w mocy podczas wykonywania procedury, a następnie przywraca jej poprzednie ustawienie.

Uwaga / Notatka

Zestaw ANSI_WARNINGS nie jest honorowany podczas przekazywania parametrów w procedurze, funkcji zdefiniowanej przez użytkownika lub podczas deklarowania i ustawiania zmiennych w instrukcji wsadowej. Jeśli na przykład zmienna jest zdefiniowana jako char(3), a następnie ustawiona na wartość większą niż trzy znaki, dane zostaną obcięte do zdefiniowanego rozmiaru, a instrukcja INSERT lub UPDATE zakończy się powodzeniem.

Ograniczenia i ograniczenia

Instrukcja CREATE PROCEDURE nie może być połączona z innymi instrukcjami Transact-SQL w jednej partii.

Poniższe instrukcje nie mogą być używane nigdzie w treści procedury składowanej.

CREATE	SET	USE
TWORZENIE AGREGACJI	USTAW SHOWPLAN_TEXT	UŻYWANIE database_name
UTWÓRZ DOMYŚLNE	USTAW SHOWPLAN_XML
TWORZENIE REGUŁY	USTAW PARSEONLY
TWORZENIE SCHEMATU	ZESTAW SHOWPLAN_ALL
TWORZENIE lub ZMIENIANIE WYZWALACZA
CREATE lub ALTER FUNCTION
TWORZENIE lub ZMIENIANIE PROCEDURY
TWORZENIE lub ZMIENIANIE WIDOKU

Procedura może odwoływać się do tabel, które jeszcze nie istnieją. W czasie tworzenia wykonywane jest tylko sprawdzanie składni. Procedura nie jest kompilowana, dopóki nie zostanie wykonana po raz pierwszy. Tylko podczas kompilacji wszystkie obiekty, do których odwołuje się procedura, są rozpoznawane. W związku z tym można pomyślnie utworzyć prawidłową procedurę, która odwołuje się do tabel, które nie istnieją; jednak procedura kończy się niepowodzeniem w czasie wykonywania, jeśli przywoływały tabele nie istnieją.

Nie można określić nazwy funkcji jako wartości domyślnej parametru lub wartości przekazanej do parametru podczas wykonywania procedury. Można jednak przekazać funkcję jako zmienną, jak pokazano w poniższym przykładzie.

-- Passing the function value as a variable.
DECLARE @CheckDate DATETIME = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

Jeśli procedura wprowadza zmiany w zdalnym wystąpieniu programu SQL Server, zmiany nie mogą zostać wycofane. Procedury zdalne nie biorą udziału w transakcjach.

Aby aparat bazy danych odwołył się do prawidłowej metody, gdy jest przeciążony w programie .NET Framework, metoda określona w klauzuli EXTERNAL NAME musi mieć następujące cechy:

• Należy zadeklarować jako metodę statyczną.
• Odbierz tę samą liczbę parametrów co liczba parametrów procedury.
• Użyj typów parametrów zgodnych z typami danych odpowiadających mu parametrów procedury programu SQL Server. Aby uzyskać informacje na temat dopasowywania typów danych programu SQL Server do typów danych programu .NET Framework, zobacz Mapowanie danych parametrów CLR.

Metadane

W poniższej tabeli wymieniono widoki wykazu i dynamiczne widoki zarządzania, których można użyć do zwracania informacji o procedurach składowanych.

View	Description
sys.sql_modules	Zwraca definicję procedury Transact-SQL. Nie można wyświetlić tekstu procedury utworzonej przy użyciu opcji SZYFROWANIE przy użyciu sys.sql_modules widoku wykazu.
sys.assembly_modules	Zwraca informacje o procedurze CLR.
sys.parameters	Zwraca informacje o parametrach zdefiniowanych w procedurze
sys.sql_expression_dependenciessys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	Zwraca obiekty, do których odwołuje się procedura.

Aby oszacować rozmiar skompilowanej procedury, użyj następujących liczników monitora wydajności.

Nazwa obiektu monitora wydajności	Nazwa licznika monitora wydajności
SQLServer: Planowanie obiektu pamięci podręcznej	Współczynnik trafień pamięci podręcznej
	Strony pamięci podręcznej
	Liczba obiektów pamięci podręcznej 1

¹ Te liczniki są dostępne dla różnych kategorii obiektów pamięci podręcznej, w tym ad hoc języka Transact-SQL, przygotowanego języka Transact-SQL, procedur, wyzwalaczy itd. Aby uzyskać więcej informacji, zobacz SQL Server, Plan Cache Object (Planowanie obiektu pamięci podręcznej).

Permissions

Wymaga CREATE PROCEDURE uprawnień w bazie danych i ALTER uprawnienia do schematu, w którym jest tworzona procedura, lub wymaga członkostwa w stałej roli bazy danych db_ddladmin.

W przypadku procedur składowanych CLR wymaga własności zestawu, do którego odwołuje się klauzula EXTERNAL NAME, lub REFERENCES uprawnienia do tego zestawu.

CREATE PROCEDURE i tabele zoptymalizowane pod kątem pamięci

Dostęp do tabel zoptymalizowanych pod kątem pamięci można uzyskać za pośrednictwem tradycyjnych i natywnie skompilowanych procedur składowanych. W większości przypadków procedury natywne są bardziej wydajne. Aby uzyskać więcej informacji, zobacz Natywnie skompilowane procedury składowane.

W poniższym przykładzie pokazano, jak utworzyć natywnie skompilowaną procedurę składowaną, która uzyskuje dostęp do tabeli dbo.Departmentszoptymalizowanej pod kątem pamięci:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id INT, @kitchen_count INT NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

UPDATE dbo.Departments
SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
WHERE ID = @dept_id
END;
GO

Nie można zmienić procedury utworzonej bez NATIVE_COMPILATION do natywnie skompilowanej procedury składowanej.

Aby zapoznać się z omówieniem możliwości programowania w natywnie skompilowanych procedurach składowanych, obsługiwanym obszarze powierzchni zapytań i operatorach, zobacz Obsługiwane funkcje dla natywnie skompilowanych modułów języka T-SQL.

Przykłady

Kategoria	Polecane elementy składni
Podstawowa składnia	TWORZENIE PROCEDURY
Przekazywanie parametrów	@parameter = wartość domyślna WYJŚCIE typ parametru wartości tabeli KURSOR RÓŻNI SIĘ
Modyfikowanie danych przy użyciu procedury składowanej	Aktualizacja
Obsługa błędów	TRY...CATCH
Zaciemnianie definicji procedury	Z SZYFROWANIEM
Wymuszanie ponownego kompilowania procedury	ZA POMOCĄ FUNKCJI REKOMPILUJ
Ustawianie kontekstu zabezpieczeń	WYKONAJ JAKO

Składnia podstawowa

Przykłady w tej sekcji przedstawiają podstawowe funkcje instrukcji CREATE PROCEDURE przy użyciu minimalnej wymaganej składni.

A. Tworzenie procedury Transact-SQL

Poniższy przykład tworzy procedurę przechowywaną, która zwraca wszystkich pracowników (podają imię i nazwiska), ich stanowiska oraz nazwy działów z widoku w bazie AdventureWorks2025. Ta procedura nie używa żadnych parametrów. W tym przykładzie przedstawiono trzy metody wykonywania procedury.

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

Procedurę uspGetEmployees można wykonać 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. Zwracanie więcej niż jednego zestawu wyników

Poniższa procedura zwraca dwa zestawy wyników.

CREATE PROCEDURE dbo.uspMultipleResults
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

C. Tworzenie procedury składowanej CLR

Poniższy przykład tworzy procedurę GetPhotoFromDB odwołującą GetPhotoFromDB się do metody LargeObjectBinary klasy w HandlingLOBUsingCLR zestawie. Przed utworzeniem HandlingLOBUsingCLR procedury zestaw jest zarejestrowany w lokalnej bazie danych. W przykładzie przyjęto założenie, że zestaw utworzony na podstawie assembly_bits.

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, bazy danych Azure SQL, bazy danych SQL w Microsoft Fabric, gdy używa się asembla utworzonego z assembly_bits.

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

Przekazywanie parametrów

Przykłady w tej sekcji pokazują, jak używać parametrów wejściowych i wyjściowych do przekazywania wartości do i z procedury składowanej.

D. Tworzenie procedury z parametrami wejściowymi

Poniższy przykład tworzy procedurę składowaną, która zwraca informacje dla określonego pracownika, przekazując wartości dla imienia i nazwiska pracownika. Ta procedura akceptuje tylko dokładne dopasowania dla przekazanych parametrów.

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

Procedurę uspGetEmployees można wykonać 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';

E. Używanie procedury z parametrami wieloznacznymi

Poniższy przykład tworzy procedurę składowaną, która zwraca informacje dla pracowników, przekazując pełne lub częściowe wartości dla imienia i nazwiska pracownika. Ten wzorzec procedury jest zgodny z przekazanymi parametrami lub, jeśli nie podano, używa ustawienia domyślnego (nazwiska rozpoczynające się literą D).

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, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

Procedurę uspGetEmployees2 można wykonać w wielu kombinacjach. W tym miejscu pokazano tylko kilka możliwych kombinacji.

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%';

F. Używanie parametrów WYJŚCIOWYch

Poniższy przykład tworzy procedurę uspGetList . Ta procedura zwraca listę produktów, które mają ceny, które nie przekraczają określonej kwoty. W przykładzie pokazano użycie wielu SELECT instrukcji i wielu OUTPUT parametrów. Parametry wyjściowe umożliwiają procedurę zewnętrzną, partię lub więcej niż jedną instrukcję Transact-SQL w celu uzyskania dostępu do wartości ustawionej podczas wykonywania procedury.

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

Wykonaj polecenie , uspGetList aby zwrócić listę produktów Adventure Works (Bikes), które kosztują mniej niż $700. Parametry OUTPUT@Cost i @ComparePrices są używane z językiem sterowania przepływu w celu zwrócenia komunikatu w oknie Komunikaty .

Uwaga / Notatka

Zmienna OUTPUT musi być zdefiniowana podczas tworzenia procedury, a także w przypadku użycia zmiennej. Nazwa parametru i nazwa zmiennej nie muszą być zgodne; jednak typ danych i pozycjonowanie parametrów muszą być zgodne, chyba że @ListPrice = jest używana zmienna.

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

Oto zestaw wyników częściowych:

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.

G. Używanie parametru wartości tabeli

W poniższym przykładzie użyto typu parametru o wartości tabeli, aby wstawić wiele wierszy do tabeli. Przykład tworzy typ parametru, deklaruje zmienną tabeli, aby się do niej odwoływać, wypełnia listę parametrów, a następnie przekazuje wartości do procedury składowanej. Procedura składowana używa wartości do wstawiania wielu wierszy do tabeli.

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2022].[Production].[Location]
       ([Name]
       , [CostRate]
       , [Availability]
       , [ModifiedDate])
    SELECT *, 0, GETDATE()
    FROM @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM
    [AdventureWorks2022].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

H. Używanie parametru kursora WYJŚCIOWEgo

W poniższym przykładzie użyto parametru kursora OUTPUT, aby przekazać kursor lokalny do procedury z powrotem do wywołującej partii, procedury lub wyzwalacza.

Najpierw utwórz procedurę, która deklaruje, a następnie otwiera kursor w Currency tabeli:

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

Następnie uruchom partię, która deklaruje lokalną zmienną kursora, wykonuje procedurę, aby przypisać kursor do zmiennej lokalnej, a następnie pobiera wiersze z kursora.

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

Modyfikowanie danych przy użyciu procedury składowanej

Przykłady w tej sekcji pokazują, jak wstawiać lub modyfikować dane w tabelach lub widokach, dołączając instrukcję Języka manipulowania danymi (DML) w definicji procedury.

I. Używanie aktualizacji w procedurze składowanej

W poniższym przykładzie użyto instrukcji UPDATE w procedurze składowanej. Procedura przyjmuje jeden parametr wejściowy i @NewHours jeden parametr @RowCountwyjściowy . Wartość @NewHours parametru jest używana w instrukcji UPDATE w celu zaktualizowania kolumny VacationHours w tabeli HumanResources.Employee. Parametr @RowCount wyjściowy służy do zwracania liczby wierszy, których dotyczy problem ze zmienną lokalną. Wyrażenie CASE jest używane w klauzuli SET w celu warunkowego określenia wartości ustawionej dla VacationHourselementu . Gdy pracownik jest wypłacany godzinowo (SalariedFlag = 0), VacationHours jest ustawiony na bieżącą liczbę godzin plus wartość określoną w @NewHours; w przeciwnym razie VacationHours jest ustawiona na wartość określoną w @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

Obsługa błędów

Przykłady w tej sekcji przedstawiają metody obsługi błędów, które mogą wystąpić podczas wykonywania procedury składowanej.

J. Użyj funkcji TRY... ŁAPAĆ

W poniższym przykładzie przy użyciu narzędzia TRY... Konstrukcja CATCH zwracająca informacje o błędach przechwycone podczas wykonywania procedury składowanej.

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;
GO
/* Intentionally generate an error by reversing the order in which rows
   are deleted from the parent and child tables. This change does not
   cause an error when the procedure definition is altered, but produces
   an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS

BEGIN TRY
  BEGIN TRANSACTION
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;

  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

Zaciemnianie definicji procedury

Przykłady w tej sekcji pokazują, jak zaciemniać definicję procedury składowanej.

K. Użyj opcji WITH ENCRYPTION

Poniższy przykład tworzy procedurę HumanResources.uspEncryptThis .

Dotyczy do: SQL Server 2008 (10.0.x) i nowszych wersji, Azure SQL Database, bazy danych SQL w Microsoft Fabric.

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber,
        VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

Opcja WITH ENCRYPTION zaciemnia definicję procedury podczas wykonywania zapytań w katalogu systemowym lub przy użyciu funkcji metadanych, jak pokazano w poniższych przykładach.

Uruchom polecenie sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Oto zestaw wyników.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Bezpośrednie wykonywanie zapytań względem sys.sql_modules widoku wykazu:

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

Oto zestaw wyników.

definition
--------------------------------
NULL

Uwaga / Notatka

Procedura sp_helptext składowana systemu nie jest obsługiwana w usłudze Azure Synapse Analytics. Zamiast tego użyj widoku wykazu obiektów sys.sql_modules.

Wymuszanie ponownego kompilowania procedury

Przykłady w tej sekcji używają klauzuli WITH RECOMPILE, aby wymusić ponowne kompilowania procedury za każdym razem, gdy jest wykonywana.

L. Użyj opcji WITH RECOMPILE

Klauzula jest przydatna WITH RECOMPILE , gdy parametry podane do procedury nie są typowe, a kiedy nowy plan wykonania nie powinien być buforowany ani przechowywany w pamięci.

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;

Ustawianie kontekstu zabezpieczeń

Przykłady w tej sekcji używają klauzuli EXECUTE AS, aby ustawić kontekst zabezpieczeń, w którym jest wykonywana procedura składowana.

M. Używanie klauzuli EXECUTE AS

W poniższym przykładzie pokazano użycie klauzuli EXECUTE AS w celu określenia kontekstu zabezpieczeń, w którym można wykonać procedurę. W tym przykładzie opcja CALLER określa, że można wykonać procedurę w kontekście użytkownika, który go wywołuje.

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

N. Tworzenie niestandardowych zestawów uprawnień

W poniższym przykładzie użyto funkcji EXECUTE AS do utworzenia uprawnień niestandardowych dla operacji bazy danych. Niektóre operacje, takie jak TRUNCATE TABLE, nie mają uprawnień do udzielenia. Włączając instrukcję TRUNCATE TABLE w procedurze składowanej i określając, że procedura jest wykonywana jako użytkownik, który ma uprawnienia do modyfikowania tabeli, można rozszerzyć uprawnienia do obcinania tabeli użytkownikowi, który udziela uprawnień EXECUTE w tej procedurze.

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

Przykłady: Azure Synapse Analytics and Analytics Platform System (PDW)

O. Tworzenie procedury składowanej, która uruchamia instrukcję SELECT

W tym przykładzie przedstawiono podstawową składnię tworzenia i uruchamiania procedury. Podczas uruchamiania partii metoda CREATE PROCEDURE musi być pierwszą instrukcją. Aby na przykład utworzyć następującą procedurę składowaną w adventureWorksPDW2022, najpierw ustaw kontekst bazy danych, a następnie uruchom instrukcję CREATE PROCEDURE.

-- Uses AdventureWorksDW database

--Run CREATE PROCEDURE as the first statement in a batch.
CREATE PROCEDURE Get10TopResellers
AS
BEGIN
    SELECT TOP (10) r.ResellerName, r.AnnualSales
    FROM DimReseller AS r
    ORDER BY AnnualSales DESC, ResellerName ASC;
END
;
GO

--Show 10 Top Resellers
EXEC Get10TopResellers;

Zobacz także

• PROCEDURA ALTEROWA (Transact-SQL)
• Język kontroli przepływu (Transact-SQL)
• Kursory
• Typy danych (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• WYKONAJ (Transact-SQL)
• WYKONAJ AS (Transact-SQL)
• Procedury składowane (aparatu bazy danych)
• sp_procoption (Transact-SQL)
• sp_recompile (Transact-SQL)
• sys.sql_moduły (Transact-SQL)
• sys.parameters (Transact-SQL)
• sys.procedures (Transact-SQL)
• sys.sql_zależności wyrażeń (Transact-SQL)
• sys.assembly_modules (Transact-SQL)
• sys.numbered_procedures (Transact-SQL)
• sys.numbered_procedure_parameters (Transact-SQL)
• OBJECT_DEFINITION (Transact-SQL)
• Tworzenie procedury składowanej
• Używanie parametrów Table-Valued (aparat bazy danych)
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)