PROCEDURE MAKEN (Transact-SQL)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft FabricMagazijn in Microsoft FabricSQL-database in Microsoft Fabric Preview

Hiermee maakt u een opgeslagen procedure voor Transact-SQL of Common Language Runtime (CLR) in SQL Server, Azure SQL Database, SQL Database in Microsoft Fabric Preview en Analytics Platform System (PDW). Opgeslagen procedures zijn vergelijkbaar met procedures in andere programmeertalen, omdat ze het volgende kunnen doen:

• Accepteer invoerparameters en retourneer meerdere waarden in de vorm van uitvoerparameters naar de aanroepende procedure of batch.
• Bevat programmeerinstructies die bewerkingen uitvoeren in de database, inclusief het aanroepen van andere procedures.
• Retourneer een statuswaarde naar een aanroepende procedure of batch om aan te geven dat de bewerking is geslaagd of mislukt (en de reden voor de fout).

Gebruik deze instructie om een permanente procedure te maken in de huidige database of een tijdelijke procedure in de tempdb database.

Ga naar Eenvoudige voorbeelden om de details van de syntaxis over te slaan en een snel voorbeeld te krijgen van een eenvoudige opgeslagen procedure.

Transact-SQL syntaxis-conventies

Syntaxis

Transact-SQL syntaxis voor opgeslagen procedures in SQL Server, Azure SQL Database, SQL Database in Microsoft Fabric Preview:

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 syntaxis voor opgeslagen CLR-procedures:

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 syntaxis voor systeemeigen gecompileerde opgeslagen procedures:

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 syntaxis voor opgeslagen procedures in Azure Synapse Analytics en Parallel Data Warehouse:

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

Transact-SQL syntaxis voor opgeslagen procedures in Microsoft Fabric:

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

Arguments

OR ALTER

Van toepassing op: Azure SQL Database, SQL-database in Microsoft Fabric Preview, SQL Server (te beginnen met SQL Server 2016 (13.x) SP1).

Hiermee wijzigt u de procedure als deze al bestaat.

schema_name

De naam van het schema waartoe de procedure behoort. Procedures zijn schemagebonden. Als er geen schemanaam wordt opgegeven wanneer de procedure wordt gemaakt, wordt automatisch het standaardschema van de gebruiker die de procedure maakt, toegewezen.

procedure_name

De naam van de procedure. Procedurenamen moeten voldoen aan de regels voor id's en moeten uniek zijn binnen het schema.

Lokale of globale tijdelijke procedures kunnen worden gemaakt met behulp van één nummerteken (#) voordat procedure_name (#procedure_name) voor lokale tijdelijke procedures en twee nummertekens voor globale tijdelijke procedures (##procedure_name). Een lokale tijdelijke procedure is alleen zichtbaar voor de verbinding die deze heeft gemaakt en wordt verbroken wanneer deze verbinding wordt gesloten. Er is een globale tijdelijke procedure beschikbaar voor alle verbindingen en wordt aan het einde van de laatste sessie verwijderd met behulp van de procedure. Tijdelijke namen kunnen niet worden opgegeven voor CLR-procedures.

De volledige naam voor een procedure of een globale tijdelijke procedure, inclusief ##, mag niet langer zijn dan 128 tekens. De volledige naam voor een lokale tijdelijke procedure, inclusief #, mag niet langer zijn dan 116 tekens.

; getal

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Een optioneel geheel getal dat wordt gebruikt om procedures van dezelfde naam te groeperen. Deze gegroepeerde procedures kunnen samen worden verwijderd met behulp van één DROP PROCEDURE-instructie.

Genummerde procedures kunnen de door de gebruiker gedefinieerde XML- of CLR-typen niet gebruiken en kunnen niet worden gebruikt in een planhandleiding.

@ parameter_name

Een parameter die in de procedure is gedeclareerd. Geef een parameternaam op met behulp van het at-teken (@) als het eerste teken. De parameternaam moet voldoen aan de regels voor id's. Parameters zijn lokaal voor de procedure; dezelfde parameternamen kunnen in andere procedures worden gebruikt.

Een of meer parameters kunnen worden gedeclareerd; het maximum is 2100. De waarde van elke gedeclareerde parameter moet door de gebruiker worden opgegeven wanneer de procedure wordt aangeroepen, tenzij een standaardwaarde voor de parameter is gedefinieerd of de waarde is ingesteld op een andere parameter. Als een procedure parameters met tabelwaarden bevat en de parameter ontbreekt in de aanroep, wordt een lege tabel doorgegeven. Parameters kunnen alleen plaats vinden van constante expressies; ze kunnen niet worden gebruikt in plaats van tabelnamen, kolomnamen of de namen van andere databaseobjecten. Zie EXECUTE (Transact-SQL)voor meer informatie.

Parameters kunnen niet worden gedeclareerd als FOR REPLICATION is opgegeven.

[ type_schema_name. ] data_type

Het gegevenstype van de parameter en het schema waartoe het gegevenstype behoort.

Richtlijnen voor Transact-SQL procedures:

• Alle Transact-SQL gegevenstypen kunnen worden gebruikt als parameters.
• U kunt het door de gebruiker gedefinieerde tabeltype gebruiken om parameters met tabelwaarden te maken. Tabelwaardeparameters kunnen alleen INVOERparameters zijn en moeten vergezeld gaan van het sleutelwoord READONLY. Zie Table-Valued Parameters (Database Engine) gebruiken voor meer informatie
• cursorgegevenstypen kunnen alleen UITVOERparameters zijn en moeten vergezeld gaan van het trefwoord WISSELENDE.

Richtlijnen voor CLR-procedures:

• Alle systeemeigen SQL Server-gegevenstypen met een equivalent in beheerde code kunnen worden gebruikt als parameters. Zie Toewijzings-CLR-parametergegevens voor meer informatie over de correspondentie tussen CLR-typen en SQL Server-systeemgegevens. Zie Gegevenstypen (Transact-SQL) voor meer informatie over gegevenstypen van het SQL Server-systeem en de bijbehorende syntaxis.

• Gegevenstypen met tabelwaarden of cursorgegevens kunnen niet worden gebruikt als parameters.

• Als het gegevenstype van de parameter een door de gebruiker gedefinieerd CLR-type is, moet u de machtiging EXECUTE hebben voor het type.

WISSELEND

Hiermee geeft u de resultatenset die wordt ondersteund als uitvoerparameter. Deze parameter wordt dynamisch samengesteld door de procedure en de inhoud ervan kan variëren. Alleen van toepassing op cursorparameters . Deze optie is niet geldig voor CLR-procedures.

standaard

Een standaardwaarde voor een parameter. Als een standaardwaarde is gedefinieerd voor een parameter, kan de procedure worden uitgevoerd zonder een waarde voor die parameter op te geven. De standaardwaarde moet een constante zijn of kan NULL zijn. De constante waarde kan in de vorm van een jokerteken zijn, zodat u het trefwoord LIKE kunt gebruiken bij het doorgeven van de parameter aan de procedure.

Standaardwaarden worden alleen vastgelegd in de sys.parameters.default kolom voor CLR-procedures. Deze kolom is NULL voor Transact-SQL procedureparameters.

OUT | UITVOER

Geeft aan dat de parameter een uitvoerparameter is. Gebruik UITVOERparameters om waarden te retourneren aan de aanroeper van de procedure. tekst-, ntekst- en afbeeldingsparameters kunnen niet worden gebruikt als UITVOERparameters, tenzij de procedure een CLR-procedure is. Een uitvoerparameter kan een tijdelijke aanduiding voor de cursor zijn, tenzij de procedure een CLR-procedure is. Een gegevenstype tabelwaarde kan niet worden opgegeven als een UITVOERparameter van een procedure.

READONLY

Geeft aan dat de parameter niet kan worden bijgewerkt of gewijzigd in de hoofdtekst van de procedure. Als het parametertype een tabelwaardetype is, moet READONLY worden opgegeven.

RECOMPILE

Geeft aan dat de database-engine geen queryplan voor deze procedure in de cache opgeslagen heeft, waardoor deze telkens wanneer deze wordt uitgevoerd, moet worden gecompileerd. Zie Een opgeslagen procedure opnieuw compileren voor meer informatie over de redenen voor het afdwingen van een hercompileren. Deze optie kan niet worden gebruikt wanneer REPLICATIE is opgegeven of voor CLR-procedures.

Als u de database-engine wilt instrueren om queryplannen voor afzonderlijke query's in een procedure te negeren, gebruikt u de hint voor reCOMPILE-query's in de definitie van de query. Zie Queryhints (Transact-SQL) voor meer informatie.

CODERING

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Geeft aan dat SQL Server de oorspronkelijke tekst van de INSTRUCTIE CREATE PROCEDURE converteert naar een verborgen indeling. De uitvoer van de verdoezeling is niet direct zichtbaar in een van de catalogusweergaven in SQL Server. Gebruikers die geen toegang hebben tot systeemtabellen of databasebestanden, kunnen de verborgen tekst niet ophalen. De tekst is echter beschikbaar voor bevoegde gebruikers die toegang hebben tot systeemtabellen via de DAC-poort of rechtstreeks toegang hebben tot databasebestanden. Gebruikers die een foutopsporingsprogramma aan het serverproces kunnen koppelen, kunnen ook de ontsleutelde procedure tijdens runtime ophalen uit het geheugen. Zie De configuratie van zichtbaarheid van metagegevens van metagegevens voor metagegevens voor meer informatie over het openen van systeemmetagegevens.

Deze optie is niet geldig voor CLR-procedures.

Procedures die met deze optie zijn gemaakt, kunnen niet worden gepubliceerd als onderdeel van SQL Server-replicatie.

EXECUTE AS-component

Hiermee geeft u de beveiligingscontext op waaronder de procedure moet worden uitgevoerd.

Voor systeemeigen gecompileerde opgeslagen procedures gelden geen beperkingen voor de EXECUTE AS component. In SQL Server 2014 (12.x) en eerdere versies worden de SELFcomponenten , OWNERen 'user_name' ondersteund met systeemeigen gecompileerde opgeslagen procedures.

Zie EXECUTE AS-component (Transact-SQL) voor meer informatie.

VOOR REPLICATIE

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Hiermee geeft u op dat de procedure wordt gemaakt voor replicatie. Daarom kan deze niet worden uitgevoerd op de abonnee. Een procedure die is gemaakt met de optie FOR REPLICATION wordt gebruikt als een procedurefilter en wordt alleen uitgevoerd tijdens de replicatie. Parameters kunnen niet worden gedeclareerd als FOR REPLICATION is opgegeven. REPLICATIE kan niet worden opgegeven voor CLR-procedures. De optie RECOMPILE wordt genegeerd voor procedures die zijn gemaakt met FOR REPLICATION.

Een FOR REPLICATION procedure heeft een objecttype RF in sys.objects en sys.procedures.

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

Een of meer Transact-SQL verklaringen die de hoofdtekst van de procedure omvatten. U kunt de optionele begin- en eindtrefwoorden gebruiken om de instructies in te sluiten. Zie de secties Aanbevolen procedures, Algemene opmerkingen en Beperkingen en Beperkingen die volgen voor meer informatie.

EXTERNE NAAM assembly_name. class_name. method_name

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Hiermee geeft u de methode van een .NET Framework-assembly voor een CLR-procedure waarnaar moet worden verwezen. class_name moet een geldige SQL Server-id zijn en moeten bestaan als een klasse in de assembly. Als de klasse een naamruimte-gekwalificeerde naam heeft die gebruikmaakt van een punt (.) om naamruimteonderdelen te scheiden, moet de klassenaam worden gescheiden met vierkante haken ([]) of aanhalingstekens (""). De opgegeven methode moet een statische methode van de klasse zijn.

SQL Server kan standaard geen CLR-code uitvoeren. U kunt databaseobjecten maken, wijzigen en verwijderen die verwijzen naar algemene runtimemodules voor taal; U kunt deze verwijzingen echter pas uitvoeren in SQL Server als u de optie Clr ingeschakeld inschakelt. Als u de optie wilt inschakelen, gebruikt u sp_configure.

ATOMISCH MET

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Geeft de uitvoering van de atomische opgeslagen procedure aan. Wijzigingen worden doorgevoerd of alle wijzigingen die worden teruggedraaid door een uitzondering te genereren. Het ATOMIC WITH-blok is vereist voor systeemeigen gecompileerde opgeslagen procedures.

Als de procedure RETURN's (expliciet via de RETURN-instructie of impliciet door het voltooien van de uitvoering), wordt het werk dat door de procedure wordt uitgevoerd, doorgevoerd. Als de procedure TROW's zijn, wordt het werk dat door de procedure wordt uitgevoerd, teruggedraaid.

XACT_ABORT is standaard ingeschakeld in een atomisch blok en kan niet worden gewijzigd. XACT_ABORT geeft aan of SQL Server de huidige transactie automatisch terugdraait wanneer een Transact-SQL-instructie een runtimefout genereert.

De volgende SET-opties zijn altijd ingeschakeld in het ATOMIC-blok en kunnen niet worden gewijzigd.

• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS

SET-opties kunnen niet worden gewijzigd binnen ATOMIC-blokken. De SET-opties in de gebruikerssessie worden niet gebruikt in het bereik van systeemeigen gecompileerde opgeslagen procedures. Deze opties worden tijdens het compileren opgelost.

BEGIN-, ROLLBACK- en COMMIT-bewerkingen kunnen niet worden gebruikt in een atomisch blok.

Er is één ATOMIC-blok per systeemeigen gecompileerde opgeslagen procedure, in het buitenste bereik van de procedure. De blokken kunnen niet worden genest. Zie Systeemeigen gecompileerde opgeslagen procedures voor meer informatie over atomische blokken.

NULL | NIET NULL

Bepaalt of null-waarden zijn toegestaan in een parameter. NULL is de standaardwaarde.

NATIVE_COMPILATION

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Geeft aan dat de procedure systeemeigen is gecompileerd. NATIVE_COMPILATION, SCHEMABINDING en EXECUTE AS kunnen in elke volgorde worden opgegeven. Zie Systeemeigen opgeslagen procedures voor meer informatie.

SCHEMABINDING

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Zorgt ervoor dat tabellen waarnaar wordt verwezen door een procedure niet kunnen worden verwijderd of gewijzigd. SCHEMABINDING is vereist in systeemeigen gecompileerde opgeslagen procedures. (Zie Systeemeigen gecompileerde opgeslagen procedures voor meer informatie.) De SCHEMABINDING-beperkingen zijn hetzelfde als voor door de gebruiker gedefinieerde functies. Zie de sectie SCHEMABINDING in CREATE FUNCTION (Transact-SQL)voor meer informatie.

LANGUAGE = [N] 'language'

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Komt overeen met de sessieoptie SET LANGUAGE (Transact-SQL). LANGUAGE = [N] 'language' is vereist.

NIVEAU VAN TRANSACTIE-ISOLATIE

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Vereist voor systeemeigen gecompileerde opgeslagen procedures. Hiermee geeft u het niveau van transactieisolatie voor de opgeslagen procedure. De opties zijn als volgt:

Zie SET TRANSACTION ISOLATION LEVEL (Transact-SQL) voor meer informatie over deze opties.

HERHAALBARE LEESBEWERKING

Hiermee geeft u op dat instructies geen gegevens kunnen lezen die zijn gewijzigd, maar nog niet zijn doorgevoerd door andere transacties. Als een andere transactie gegevens wijzigt die door de huidige transactie zijn gelezen, mislukt de huidige transactie.

SERIALIZABLE

Hiermee geeft u het volgende op:

• Instructies kunnen geen gegevens lezen die zijn gewijzigd, maar nog niet zijn doorgevoerd door andere transacties.
• Als een andere transactie gegevens wijzigt die door de huidige transactie zijn gelezen, mislukt de huidige transactie.
• Als een andere transactie nieuwe rijen met sleutelwaarden invoegt die in het bereik van sleutels zouden vallen, gelezen door instructies in de huidige transactie, mislukt de huidige transactie.

MOMENTOPNAME

Hiermee geeft u op dat gegevens die worden gelezen door een instructie in een transactie de transactioneel consistente versie is van de gegevens die aan het begin van de transactie bestonden.

DATEFIRST = getal

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Hiermee geeft u de eerste dag van de week tot een getal van 1 tot en met 7. DATEFIRST is een optie. Als deze niet is opgegeven, wordt de instelling afgeleid van de opgegeven taal.

Zie SET DATEFIRST (Transact-SQL)voor meer informatie.

DATUMNOTATIE = notatie

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

Hiermee geeft u de volgorde op van de onderdelen maand, dag en jaar voor het interpreteren van datum, smalldatetime, datum/tijd, datum/tijd2 en tekenreeksen voor datum/tijdoffset . DATEFORMAT is optioneel. Als deze niet is opgegeven, wordt de instelling afgeleid van de opgegeven taal.

Zie SET DATEFORMAT (Transact-SQL)voor meer informatie.

DELAYED_DURABILITY = { UIT | ON }

Van toepassing op: SQL Server 2014 (12.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

SQL Server-transactiedoorvoeringen kunnen volledig duurzaam, de standaardinstelling of vertraagd duurzaam zijn.

Zie Duurzaamheid van besturingstransacties voor meer informatie.

Eenvoudige voorbeelden

Hier volgen twee snelle voorbeelden om u te helpen aan de slag te gaan: SELECT DB_NAME() AS ThisDB; retourneert de naam van de huidige database. U kunt deze instructie verpakken in een opgeslagen procedure, zoals:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Roep de winkelprocedure aan met de instructie: EXEC What_DB_is_this;

Iets complexer is het opgeven van een invoerparameter om de procedure flexibeler te maken. Voorbeeld:

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

Geef een database-id-nummer op wanneer u de procedure aanroept. Retourneert EXEC What_DB_is_that 2;bijvoorbeeld tempdb .

Zie Voorbeelden aan het einde van dit artikel voor nog veel meer voorbeelden.

Beste praktijken

Hoewel dit geen volledige lijst met aanbevolen procedures is, kunnen deze suggesties de prestaties van procedures verbeteren.

• Gebruik de instructie SET NOCOUNT ON als de eerste instructie in de hoofdtekst van de procedure. Dat wil gezegd, plaats het net na het AS-trefwoord. Hiermee worden berichten uitgeschakeld die SQL Server terugstuurt naar de client nadat de instructies SELECT, INSERT, UPDATE, MERGE en DELETE zijn uitgevoerd. Hierdoor blijft de uitvoer tot een minimum beperkt voor duidelijkheid. Er is echter geen meetbaar prestatievoordeel op de hardware van vandaag. Zie SET NOCOUNT (Transact-SQL)voor meer informatie.
• Gebruik schemanamen bij het maken of verwijzen naar databaseobjecten in de procedure. Het duurt minder verwerkingstijd voordat de database-engine objectnamen heeft omgezet als er niet in meerdere schema's hoeft te worden gezocht. Het voorkomt ook problemen met machtigingen en toegang die worden veroorzaakt door het standaardschema van een gebruiker dat wordt toegewezen wanneer objecten worden gemaakt zonder het schema op te geven.
• Vermijd het verpakken van functies rond kolommen die zijn opgegeven in de WHERE- en JOIN-componenten. Hierdoor worden de kolommen niet-deterministisch en voorkomt u dat de queryprocessor indexen gebruikt.
• Vermijd het gebruik van scalaire functies in SELECT-instructies die veel rijen met gegevens retourneren. Omdat de scalaire functie op elke rij moet worden toegepast, is het resulterende gedrag vergelijkbaar met verwerking op basis van rijen en verslechtert de prestaties.
• Vermijd het gebruik van SELECT *. Geef in plaats daarvan de vereiste kolomnamen op. Dit kan voorkomen dat sommige database-enginefouten de uitvoering van de procedure stoppen. Een instructie die bijvoorbeeld SELECT * gegevens retourneert uit een tabel met 12 kolommen en die gegevens vervolgens invoegt in een tijdelijke tabel van 12 kolommen, slaagt totdat het aantal of de volgorde van kolommen in een van beide tabellen wordt gewijzigd.
• Vermijd het verwerken of retourneren van te veel gegevens. Verfijn de resultaten zo vroeg mogelijk in de procedurecode, zodat alle volgende bewerkingen die door de procedure worden uitgevoerd, worden uitgevoerd met behulp van de kleinste gegevensset die mogelijk is. Alleen de essentiële gegevens verzenden naar de clienttoepassing. Het is efficiënter dan het verzenden van extra gegevens via het netwerk en het afdwingen van de clienttoepassing om onnodig grote resultatensets te doorlopen.
• Gebruik expliciete transacties met behulp van BEGIN/COMMIT TRANSACTION en houd transacties zo kort mogelijk. Langere transacties betekenen langere recordvergrendeling en een groter potentieel voor impasses.
• Gebruik de Transact-SQL TRY... CATCH-functie voor foutafhandeling binnen een procedure. PROBEREN... CATCH kan een heel blok Transact-SQL instructies inkapselen. Dit zorgt niet alleen voor minder prestatieoverhead, maar maakt foutrapportage ook nauwkeuriger met aanzienlijk minder programmeren.
• Gebruik het trefwoord DEFAULT voor alle tabelkolommen waarnaar wordt verwezen door CREATE TABLE of ALTER TABLE Transact-SQL instructies in de hoofdtekst van de procedure. Dit voorkomt dat NULL wordt doorgegeven aan kolommen die geen null-waarden toestaan.
• Gebruik NULL of NOT NULL voor elke kolom in een tijdelijke tabel. De opties ANSI_DFLT_ON en ANSI_DFLT_OFF bepalen hoe de database-engine de null- of NOT NULL-kenmerken toewijst aan kolommen wanneer deze kenmerken niet zijn opgegeven in de instructie CREATE TABLE of ALTER TABLE. Als een verbinding een procedure uitvoert met verschillende instellingen voor deze opties dan de verbinding die de procedure heeft gemaakt, kunnen de kolommen van de tabel die voor de tweede verbinding zijn gemaakt, verschillende null-waarden hebben en verschillend gedrag vertonen. Als NULL of NIET NULL expliciet wordt opgegeven voor elke kolom, worden de tijdelijke tabellen gemaakt met dezelfde null-baarheid voor alle verbindingen die de procedure uitvoeren.
• Gebruik wijzigingsinstructies waarmee null-waarden worden geconverteerd en logica worden opgenomen waarmee rijen met null-waarden uit query's worden geëlimineerd. Houd er rekening mee dat NULL in Transact-SQL geen lege waarde of niets is. Het is een tijdelijke aanduiding voor een onbekende waarde en kan onverwacht gedrag veroorzaken, met name bij het uitvoeren van query's op resultatensets of het gebruik van STATISTISCHE functies.
• Gebruik de operator UNION ALL in plaats van de operator UNION of OR, tenzij er een specifieke behoefte is aan afzonderlijke waarden. De operator UNION ALL vereist minder verwerkingsoverhead omdat duplicaten niet uit de resultatenset worden gefilterd.

Opmerkingen

Er is geen vooraf gedefinieerde maximale grootte van een procedure.

Variabelen die in de procedure zijn opgegeven, kunnen door de gebruiker gedefinieerde of systeemvariabelen zijn, zoals @@SPID.

Wanneer een procedure voor het eerst wordt uitgevoerd, wordt deze gecompileerd om een optimaal toegangsplan te bepalen om de gegevens op te halen. Volgende uitvoeringen van de procedure kunnen het al gegenereerde plan opnieuw gebruiken als het nog steeds in de plancache van de database-engine blijft.

Een of meer procedures kunnen automatisch worden uitgevoerd wanneer SQL Server wordt gestart. De procedures moeten worden gemaakt door de systeembeheerder in de master database en uitgevoerd onder de vaste serverrol sysadmin als achtergrondproces. De procedures kunnen geen invoer- of uitvoerparameters hebben. Zie Een opgeslagen procedure uitvoeren voor meer informatie.

Procedures worden genest wanneer een procedure een andere aanroept of beheerde code uitvoert door te verwijzen naar een CLR-routine, -type of aggregatie. Procedures en beheerde codeverwijzingen kunnen maximaal 32 niveaus worden genest. Het nestniveau neemt met één toe wanneer de aangeroepen procedure- of beheerde codeverwijzing begint met uitvoeren en afneemt met één wanneer de aangeroepen procedure of beheerde codeverwijzing de uitvoering voltooit. Methoden die vanuit de beheerde code worden aangeroepen, tellen niet mee voor de limiet voor nestniveau. Wanneer een opgeslagen CLR-procedure echter bewerkingen voor gegevenstoegang uitvoert via de beheerde SQL Server-provider, wordt er een extra nestniveau toegevoegd in de overgang van beheerde code naar SQL.

Als u het maximale nestniveau probeert te overschrijden, mislukt de hele aanroepketen. U kunt de functie @@NESTLEVEL gebruiken om het nestniveau van de huidige opgeslagen procedure te retourneren.

Interoperabiliteit

De database-engine slaat de instellingen van zowel SET-QUOTED_IDENTIFIER als SET-ANSI_NULLS op wanneer een Transact-SQL procedure wordt gemaakt of gewijzigd. Deze oorspronkelijke instellingen worden gebruikt wanneer de procedure wordt uitgevoerd. Daarom worden instellingen voor clientsessies voor SET-QUOTED_IDENTIFIER en SET-ANSI_NULLS genegeerd wanneer de procedure wordt uitgevoerd.

Andere SET-opties, zoals SET ARITHABORT, SET ANSI_WARNINGS of SET-ANSI_PADDINGS worden niet opgeslagen wanneer een procedure wordt gemaakt of gewijzigd. Als de logica van de procedure afhankelijk is van een bepaalde instelling, moet u aan het begin van de procedure een SET-instructie opnemen om de juiste instelling te garanderen. Wanneer een SET-instructie wordt uitgevoerd vanuit een procedure, blijft de instelling alleen van kracht totdat de procedure is uitgevoerd. De instelling wordt vervolgens hersteld naar de waarde die de procedure had toen deze werd aangeroepen. Hierdoor kunnen afzonderlijke clients de gewenste opties instellen zonder dat dit van invloed is op de logica van de procedure.

Elke SET-instructie kan binnen een procedure worden opgegeven, behalve SET-SHOWPLAN_TEXT en SET-SHOWPLAN_ALL. Dit moeten de enige instructies in de batch zijn. De gekozen optie SET blijft van kracht tijdens de uitvoering van de procedure en wordt vervolgens teruggezet naar de vorige instelling.

Beperkingen en beperkingen

De INSTRUCTIE CREATE PROCEDURE kan niet worden gecombineerd met andere Transact-SQL instructies in één batch.

De volgende instructies kunnen nergens in de hoofdtekst van een opgeslagen procedure worden gebruikt.

Een procedure kan verwijzen naar tabellen die nog niet bestaan. Tijdens het maken wordt alleen de syntaxiscontrole uitgevoerd. De procedure wordt pas gecompileerd als deze voor het eerst wordt uitgevoerd. Alleen tijdens de compilatie worden alle objecten waarnaar wordt verwezen in de procedure opgelost. Daarom kan er een syntactisch juiste procedure worden gemaakt die verwijst naar tabellen die niet bestaan; De procedure mislukt echter tijdens de uitvoering als de tabellen waarnaar wordt verwezen niet bestaan.

U kunt geen functienaam opgeven als een standaardwaarde voor parameters of als de waarde die wordt doorgegeven aan een parameter bij het uitvoeren van een procedure. U kunt echter een functie doorgeven als een variabele, zoals wordt weergegeven in het volgende voorbeeld.

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

Als de procedure wijzigingen aanbrengt in een extern exemplaar van SQL Server, kunnen de wijzigingen niet worden teruggedraaid. Externe procedures nemen geen deel aan transacties.

Als de database-engine naar de juiste methode verwijst wanneer deze overbelast is in .NET Framework, moet de methode die is opgegeven in de COMPONENT EXTERNAL NAME de volgende kenmerken hebben:

• Worden gedeclareerd als een statische methode.
• Ontvang hetzelfde aantal parameters als het aantal parameters van de procedure.
• Gebruik parametertypen die compatibel zijn met de gegevenstypen van de bijbehorende parameters van de SQL Server-procedure. Zie Toewijzings-CLR-parametergegevens voor informatie over het koppelen van SQL Server-gegevenstypen aan de .NET Framework-gegevens.

Metagegevens

De volgende tabel bevat de catalogusweergaven en dynamische beheerweergaven die u kunt gebruiken om informatie over opgeslagen procedures te retourneren.

Als u de grootte van een gecompileerde procedure wilt schatten, gebruikt u de volgende prestatiemeteritems.

¹ Deze tellers zijn beschikbaar voor verschillende categorieën cacheobjecten, waaronder ad-hoc Transact-SQL, voorbereide Transact-SQL, procedures, triggers, enzovoort. Zie SQL Server, Plan Cache Object voor meer informatie.

Permissions

Vereist CREATE PROCEDURE machtigingen in de database en ALTER machtigingen voor het schema waarin de procedure wordt gemaakt, of vereist lidmaatschap van de db_ddladmin vaste databaserol.

Voor opgeslagen CLR-procedures is het eigendom van de assembly vereist waarnaar wordt verwezen in de COMPONENT EXTERNAL NAME of REFERENCES de machtiging voor die assembly.

CREATE PROCEDURE en tabellen die zijn geoptimaliseerd voor geheugen

Tabellen die zijn geoptimaliseerd voor geheugen, kunnen worden geopend via zowel traditionele als systeemeigen opgeslagen procedures. Systeemeigen procedures zijn in de meeste gevallen de efficiëntere manier. Zie Systeemeigen opgeslagen procedures voor meer informatie.

In het volgende voorbeeld ziet u hoe u een systeemeigen gecompileerde opgeslagen procedure maakt die toegang heeft tot een tabel dbo.Departmentsdie is geoptimaliseerd voor geheugen:

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

Een procedure die zonder NATIVE_COMPILATION is gemaakt, kan niet worden gewijzigd in een systeemeigen opgeslagen procedure.

Zie Ondersteunde functies voor systeemeigen gecompileerde T-SQL-modules voor een bespreking van programmeerbaarheid in systeemeigen gecompileerde opgeslagen procedures, ondersteunde queryoppervlakken en operators.

Voorbeelden

Basissyntaxis

Voorbeelden in deze sectie laten de basisfunctionaliteit van de INSTRUCTIE CREATE PROCEDURE zien met behulp van de minimaal vereiste syntaxis.

Eén. Een Transact-SQL-procedure maken

In het volgende voorbeeld wordt een opgeslagen procedure gemaakt waarmee alle werknemers (voor- en achternamen), hun functietitels en hun afdelingsnamen worden geretourneerd vanuit een weergave in de Database AdventureWorks2022. In deze procedure worden geen parameters gebruikt. In het voorbeeld ziet u vervolgens drie methoden voor het uitvoeren van de procedure.

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

SELECT * FROM HumanResources.vEmployeeDepartment;

De uspGetEmployees procedure kan op de volgende manieren worden uitgevoerd:

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

B. Meer dan één resultatenset retourneren

Met de volgende procedure worden twee resultatensets geretourneerd.

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

C. Een opgeslagen CLR-procedure maken

In het volgende voorbeeld wordt de GetPhotoFromDB procedure gemaakt die verwijst naar de GetPhotoFromDB methode van de LargeObjectBinary klasse in de HandlingLOBUsingCLR assembly. Voordat de procedure wordt gemaakt, wordt de HandlingLOBUsingCLR assembly geregistreerd in de lokale database. In het voorbeeld wordt ervan uitgegaan dat er een assembly is gemaakt op basis van assembly_bits.

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview, wanneer u een assembly gebruikt die is gemaakt op basis van 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

Parameters doorgeven

Voorbeelden in deze sectie laten zien hoe u invoer- en uitvoerparameters gebruikt om waarden door te geven aan en van een opgeslagen procedure.

D. Een procedure maken met invoerparameters

In het volgende voorbeeld wordt een opgeslagen procedure gemaakt waarmee informatie voor een specifieke werknemer wordt geretourneerd door waarden door te geven voor de voor- en achternaam van de werknemer. Deze procedure accepteert alleen exacte overeenkomsten voor de doorgegeven parameters.

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

De uspGetEmployees procedure kan op de volgende manieren worden uitgevoerd:

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. Een procedure gebruiken met jokertekenparameters

In het volgende voorbeeld wordt een opgeslagen procedure gemaakt waarmee gegevens voor werknemers worden geretourneerd door volledige of gedeeltelijke waarden door te geven voor de voor- en achternaam van de werknemer. Dit procedurepatroon komt overeen met de parameters die zijn doorgegeven of, indien niet opgegeven, de vooraf ingestelde standaardwaarde (achternamen die beginnen met de letter 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;

De uspGetEmployees2 procedure kan in veel combinaties worden uitgevoerd. Hier worden slechts enkele mogelijke combinaties weergegeven.

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. UITVOERparameters gebruiken

In het volgende voorbeeld wordt de uspGetList procedure gemaakt. Deze procedure retourneert een lijst met producten met prijzen die geen opgegeven bedrag overschrijden. In het voorbeeld ziet u hoe u meerdere SELECT instructies en meerdere OUTPUT parameters gebruikt. Uitvoerparameters maken een externe procedure, een batch of meer dan één Transact-SQL instructie mogelijk voor toegang tot een waardeset tijdens de uitvoering van de procedure.

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

Voer uit uspGetList om een lijst met Adventure Works-producten (Bikes) te retourneren die lager zijn dan $700. De OUTPUT parameters @Cost en @ComparePrices worden gebruikt met de besturings-of-stroomtaal om een bericht in het venster Berichten te retourneren.

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

Dit is de gedeeltelijke resultatenset:

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. Een parameter met tabelwaarde gebruiken

In het volgende voorbeeld wordt een parametertype met tabelwaarde gebruikt om meerdere rijen in een tabel in te voegen. In het voorbeeld wordt het parametertype gemaakt, wordt een tabelvariabele gede declareert om ernaar te verwijzen, wordt de lijst met parameters ingevuld en worden de waarden vervolgens doorgegeven aan een opgeslagen procedure. De opgeslagen procedure gebruikt de waarden om meerdere rijen in een tabel in te voegen.

/* 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. Een PARAMETER UITVOERcursor gebruiken

In het volgende voorbeeld wordt de parameter OUTPUT cursor gebruikt om een cursor door te geven die lokaal is voor een procedure terug naar de aanroepende batch, procedure of trigger.

Maak eerst de procedure die declareert en opent vervolgens een cursor in de Currency tabel:

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

Voer vervolgens een batch uit die een lokale cursorvariabele declareert, voert de procedure uit om de cursor toe te wijzen aan de lokale variabele en haalt vervolgens de rijen van de cursor op.

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

Gegevens wijzigen met behulp van een opgeslagen procedure

Voorbeelden in deze sectie laten zien hoe u gegevens in tabellen of weergaven invoegt of wijzigt door een DML-instructie (Data Manipulation Language) op te geven in de definitie van de procedure.

I. UPDATE gebruiken in een opgeslagen procedure

In het volgende voorbeeld wordt een UPDATE-instructie gebruikt in een opgeslagen procedure. De procedure heeft één invoerparameter @NewHours en één uitvoerparameter @RowCount. De @NewHours parameterwaarde wordt gebruikt in de INSTRUCTIE UPDATE om de kolom VacationHours in de tabel HumanResources.Employeebij te werken. De @RowCount uitvoerparameter wordt gebruikt om het aantal rijen te retourneren dat is beïnvloed door een lokale variabele. Een CASE-expressie wordt gebruikt in de SET-component om de waarde die is ingesteld VacationHoursvoor voorwaardelijk te bepalen. Wanneer de werknemer per uur wordt betaald (SalariedFlag = 0), VacationHours wordt ingesteld op het huidige aantal uren plus de waarde die is opgegeven in @NewHours; anders wordt VacationHours ingesteld op de waarde die is opgegeven in @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;

Foutafhandeling

Voorbeelden in deze sectie laten methoden zien voor het afhandelen van fouten die kunnen optreden wanneer de opgeslagen procedure wordt uitgevoerd.

J. TRY gebruiken... VANGEN

Het volgende voorbeeld met behulp van de TRY... CATCH-constructie voor het retourneren van foutinformatie die is opgetreden tijdens de uitvoering van een opgeslagen procedure.

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;

De proceduredefinitie verdoezelen

In deze sectie ziet u hoe u de definitie van de opgeslagen procedure kunt verdoezelen.

K. De optie WITH ENCRYPTION gebruiken

In het volgende voorbeeld wordt de HumanResources.uspEncryptThis procedure gemaakt.

Van toepassing op: SQL Server 2008 (10.0.x) en latere versies, Azure SQL Database, SQL Database in Microsoft Fabric Preview.

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

Met WITH ENCRYPTION de optie wordt de definitie van de procedure verborgen bij het opvragen van de systeemcatalogus of het gebruik van metagegevensfuncties, zoals wordt weergegeven in de volgende voorbeelden.

Uitvoeren sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Hier is het resultatenoverzicht.

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

Rechtstreeks een query uitvoeren op de sys.sql_modules catalogusweergave:

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

Hier is het resultatenoverzicht.

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

Forceer de procedure om opnieuw te compileren

In voorbeelden in deze sectie wordt de COMPONENT WITH RECOMPILE gebruikt om af te dwingen dat de procedure telkens opnieuw wordt gecompileerd wanneer deze wordt uitgevoerd.

L. De optie WITH RECOMPILE gebruiken

De WITH RECOMPILE component is handig wanneer de parameters die aan de procedure worden geleverd, niet gebruikelijk zijn en wanneer een nieuw uitvoeringsplan niet in de cache moet worden opgeslagen of opgeslagen in het geheugen.

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;

De beveiligingscontext instellen

In deze sectie wordt de EXECUTE AS-component gebruikt om de beveiligingscontext in te stellen waarin de opgeslagen procedure wordt uitgevoerd.

M. De EXECUTE AS-component gebruiken

In het volgende voorbeeld ziet u hoe u de EXECUTE AS-component gebruikt om de beveiligingscontext op te geven waarin een procedure kan worden uitgevoerd. In het voorbeeld geeft de optie CALLER aan dat de procedure kan worden uitgevoerd in de context van de gebruiker die deze aanroept.

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. Aangepaste machtigingensets maken

In het volgende voorbeeld wordt EXECUTE AS gebruikt om aangepaste machtigingen te maken voor een databasebewerking. Sommige bewerkingen, zoals TRUNCATE TABLE, hebben geen toekenningsmachtigingen. Door de instructie TRUNCATE TABLE in een opgeslagen procedure op te nemen en op te geven dat deze procedure wordt uitgevoerd als een gebruiker met machtigingen voor het wijzigen van de tabel, kunt u de machtigingen uitbreiden om de tabel af te kapen naar de gebruiker die u EXECUTE-machtigingen voor de procedure verleent.

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

Voorbeelden: Azure Synapse Analytics and Analytics Platform System (PDW)

O. Een opgeslagen procedure maken waarmee een SELECT-instructie wordt uitgevoerd

In dit voorbeeld ziet u de basissyntaxis voor het maken en uitvoeren van een procedure. Bij het uitvoeren van een batch moet CREATE PROCEDURE de eerste instructie zijn. Als u bijvoorbeeld de volgende opgeslagen procedure in AdventureWorksPDW2022 wilt maken, stelt u eerst de databasecontext in en voert u vervolgens de instructie CREATE PROCEDURE uit.

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

Zie ook

• ALTER PROCEDURE (Transact-SQL)
• Control-of-Flow Language (Transact-SQL)
• Cursors
• Gegevenstypen (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• DROP PROCEDURE (Transact-SQL)
• UITVOEREN (Transact-SQL)
• EXECUTE AS (Transact-SQL)
• Opgeslagen Procedures (Database Engine)
• sp_procoption (Transact-SQL)
• sp_recompile (Transact-SQL)
• sys.sql_modules (Transact-SQL)
• sys.parameters (Transact-SQL)
• sys.procedures (Transact-SQL)
• sys.sql_expression_afhankelijkheden (Transact-SQL)
• sys.assembly_modules (Transact-SQL)
• sys.numbered_procedures (Transact-SQL)
• sys.numbered_procedure_parameters (Transact-SQL)
• OBJECT_DEFINITION (Transact-SQL)
• Een opgeslagen procedure maken
• Table-Valued parameters (database-engine) gebruiken
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)