LÉTREHOZÁSI ELJÁRÁS (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse AnalyticsElemzési platformrendszer (PDW)SQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben

Létrehoz egy Transact-SQL vagy közös nyelvi futóidő (CLR) tárolt eljárást SQL Serverben, Azure SQL Database-ben, SQL adatbázisban a Microsoft Fabric-ben és az Analytics Platform Systemben (PDW). A tárolt eljárások hasonlóak más programozási nyelvek eljárásaihoz, amelyekben a következőkre képesek:

• Fogadja el a bemeneti paramétereket, és adjon vissza több értéket kimeneti paraméterek formájában a hívási eljárásnak vagy a kötegnek.
• Olyan programozási utasításokat tartalmaz, amelyek műveleteket hajtanak végre az adatbázisban, beleértve az egyéb eljárások meghívását is.
• Állapotértéket ad vissza egy hívási eljárásnak vagy kötegnek, amely jelzi a sikert vagy a hibát (és a hiba okát).

Ezzel az utasítással állandó eljárást hozhat létre az aktuális adatbázisban, vagy ideiglenes eljárást az tempdb adatbázisban.

Megjegyzés:

A .NET-keretrendszer CLR SQL Serverbe való integrálását ebben a témakörben tárgyaljuk. A CLR integráció nem vonatkozik Azure SQL Database-re vagy SQL adatbázisra a Microsoft Fabric-ben.

Az egyszerű példákra ugrással kihagyhatja a szintaxis részleteit, és gyors példát kaphat egy alapszintű tárolt eljárásra.

Transact-SQL szintaxis konvenciók

Szemantika

Transact-SQL syntax for stored procedures in SQL Server, Azure SQL Database, SQL database in 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 CLR által tárolt eljárások szintaxisa:

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 natívan lefordított tárolt eljárások szintaxisa:

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 azure Synapse Analyticsben és párhuzamos adattárházban tárolt eljárások szintaxisa:

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

Transact-SQL Microsoft Fabricben tárolt eljárások szintaxisa:

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

Arguments

VAGY ALTER

Érvényes: Azure SQL Database, SQL adatbázis Microsoft Fabric-ben, SQL Server (SQL Server 2016 (13.x) SP1-től kezdve).

Módosítja az eljárást, ha már létezik.

schema_name

Annak a sémának a neve, amelyhez az eljárás tartozik. Az eljárások sémaalapúak. Ha az eljárás létrehozásakor nincs megadva sémanév, a rendszer automatikusan hozzárendeli az eljárást létrehozó felhasználó alapértelmezett sémáját.

procedure_name

Az eljárás neve. Az eljárásneveknek meg kell felelniük az azonosítókra vonatkozó szabályoknak, és egyedinek kell lenniük a sémában.

Caution

Ne használja az előtagot az sp_ elnevezési eljárások során. Az SQL Server ezt az előtagot használja a rendszereljárások kijelöléséhez. Az előtag használata esetén az alkalmazáskód megszakadhat, ha egy azonos nevű rendszerművelet történik.

Helyi vagy globális ideiglenes eljárások hozhatók létre egy számjel (#) használatával, mielőtt procedure_name (#procedure_name) helyi ideiglenes eljárásokhoz, és két számjelet a globális ideiglenes eljárásokhoz (##procedure_name). A helyi ideiglenes eljárás csak az azt létrehozó kapcsolat számára látható, és a kapcsolat bezárásakor megszakad. Egy globális ideiglenes eljárás minden kapcsolat számára elérhető, és az eljárás használatával az utolsó munkamenet végén megszakad. A CLR-eljárásokhoz nem adható meg ideiglenes név.

Egy eljárás vagy egy globális ideiglenes eljárás teljes neve, beleértve a ##128 karaktert sem haladhatja meg. A helyi ideiglenes eljárás teljes neve, beleértve a #116 karaktert sem haladhatja meg.

; szám

Alkalmazható: SQL Server 2008 (10.0.x) és újabb verziók, Azure SQL Database, SQL adatbázis Microsoft Fabric-ben.

Nem kötelező egész szám, amely az azonos nevű eljárások csoportosítására szolgál. Ezek a csoportosított eljárások egy DROP PROCEDURE utasítással együtt elvethetők.

Megjegyzés:

Ez a funkció az SQL Server egy későbbi verziójában lesz eltávolítva. Ne használja ezt a funkciót az új fejlesztési munkában, és tervezze meg a funkciót jelenleg használó alkalmazások módosítását.

A számozott eljárások nem használhatják a felhasználó által definiált XML- vagy CLR-típusokat, és nem használhatók a terv útmutatójában.

@ parameter_name

Az eljárásban deklarált paraméter. Adjon meg egy paraméternevet úgy, hogy első karakterként az at sign (@) karaktert használja. A paraméter nevének meg kell felelnie az azonosítókra vonatkozó szabályoknak. A paraméterek helyiek az eljáráshoz; ugyanezek a paraméternevek más eljárásokban is használhatók.

Egy vagy több paraméter deklarálható; a maximális érték 2100. Az egyes deklarált paraméterek értékét a felhasználónak kell megadnia az eljárás meghívásakor, kivéve, ha a paraméter alapértelmezett értéke meg van határozva, vagy az érték egy másik paraméternek felel meg. Ha egy eljárás táblaértékkel rendelkező paramétereket tartalmaz, és a paraméter hiányzik a hívásból, a függvény egy üres táblát ad át. A paraméterek csak állandó kifejezések helyét vehetik át; nem használhatók táblanevek, oszlopnevek vagy más adatbázis-objektumok nevei helyett. További információ: EXECUTE (Transact-SQL).

A paraméterek nem deklarálhatók, ha a REPLIKÁCIÓ PARAMÉTER meg van adva.

[ type_schema_name. ] data_type

A paraméter adattípusa és annak a sémának az adattípusa, amelyhez az adattípus tartozik.

Irányelvek Transact-SQL eljárásokhoz:

• Minden Transact-SQL adattípus használható paraméterként.
• A felhasználó által definiált táblatípussal táblaértékű paramétereket hozhat létre. A táblaértékkel rendelkező paraméterek csak INPUT paraméterek lehetnek, és a READONLY kulcsszóval együtt kell lenniük. További információ: Table-Valued paraméterek használata (adatbázismotor)
• a kurzor adattípusai csak KIMENETI paraméterek lehetnek, és a VÁLTOZÓ kulcsszónak kell kísérnie.

A CLR-eljárásokra vonatkozó irányelvek:

• A felügyelt kódban egyenértékű natív SQL Server-adattípusok paraméterként használhatók. A CLR-típusok és az SQL Server rendszer adattípusai közötti megfeleltetésről további információt a CLR-paraméterek adatainak leképezése című témakörben talál. Az SQL Server rendszer adattípusairól és szintaxisukról további információt az Adattípusok (Transact-SQL) című témakörben talál.

• A táblaértékelt vagy a kurzor adattípusai nem használhatók paraméterekként.

• Ha a paraméter adattípusa felhasználó által definiált CLR-típus, akkor a típushoz VÉGREHAJTÁSI engedéllyel kell rendelkeznie.

VÁLTAKOZÓ

A kimeneti paraméterként támogatott eredményhalmazt adja meg. Ezt a paramétert az eljárás dinamikusan építi ki, és tartalma eltérő lehet. Csak a kurzorparaméterekre vonatkozik. Ez a beállítás nem érvényes a CLR-eljárásokra.

alapértelmezett

Egy paraméter alapértelmezett értéke. Ha egy paraméterhez alapértelmezett érték van definiálva, az eljárás az adott paraméter értékének megadása nélkül is végrehajtható. Az alapértelmezett értéknek állandónak vagy NULL értékűnek kell lennie. Az állandó érték helyettesítő karakter formájában is lehet, így a LIKE kulcsszót használhatja a paraméternek az eljárásba való továbbításakor.

Az alapértelmezett értékek csak a CLR-eljárások esetében vannak rögzítve az sys.parameters.default oszlopban. Ez az oszlop NULL Transact-SQL eljárásparaméterekhez.

KI | HOZAM

Azt jelzi, hogy a paraméter kimeneti paraméter. A KIMENETI paraméterekkel értékeket ad vissza az eljárás hívójának. A szöveges, az ntext- és a képparaméterek nem használhatók KIMENETI paraméterekként, kivéve, ha az eljárás CLR-eljárás. A kimeneti paraméter lehet kurzorhelyőrző, kivéve, ha az eljárás CLR-eljárás. A táblaérték adattípus nem adható meg az eljárás KIMENETI paramétereként.

ÍRÁSVÉDETT

Azt jelzi, hogy a paraméter nem frissíthető vagy módosítható az eljárás törzsében. Ha a paramétertípus tábla-érték típusú, akkor a READONLY értéket kell megadni.

RECOMPILE

Azt jelzi, hogy az adatbázismotor nem gyorsítótárazza az eljárás lekérdezési tervét, ezért minden egyes végrehajtáskor fordításra kényszeríti. Az újrafordítás okával kapcsolatos további információkért lásd: Tárolt eljárás újrafordítása. Ez a beállítás nem használható a REPLIKÁCIÓ vagy a CLR-eljárások megadásakor.

Ha arra szeretné utasítani az adatbázismotort, hogy az eljáráson belül elvetje az egyes lekérdezések lekérdezési terveit, használja a RECOMPILE lekérdezési tippet a lekérdezés definíciójában. További információ: Lekérdezési tippek (Transact-SQL).

TITKOSÍTÁS

Alkalmazható: SQL Server 2008 (10.0.x) és újabb verziók, Azure SQL Database, SQL adatbázis Microsoft Fabric-ben.

Azt jelzi, hogy az SQL Server a CREATE PROCEDURE utasítás eredeti szövegét elhomályosított formátummá alakítja. Az obfuscation kimenete nem látható közvetlenül az SQL Server katalógusnézeteiben. Azok a felhasználók, akik nem férnek hozzá a rendszertáblákhoz vagy adatbázisfájlokhoz, nem tudják lekérni az elhomályosított szöveget. A szöveg azonban elérhető a jogosultsággal rendelkező felhasználók számára, akik a DAC-porton keresztül férhetnek hozzá a rendszertáblákhoz, vagy közvetlenül hozzáférhetnek az adatbázisfájlokhoz. Emellett azok a felhasználók, akik csatlakoztathatnak egy hibakeresőt a kiszolgálói folyamathoz, futásidőben lekérhetik a visszafejtött eljárást a memóriából. A rendszer metaadatainak eléréséről további információt a Metaadatok láthatósági konfigurációja című témakörben talál.

Ez a beállítás nem érvényes a CLR-eljárásokra.

Az ezzel a beállítással létrehozott eljárások nem tehetők közzé az SQL Server-replikáció részeként.

EXECUTE AS záradék

Megadja az eljárást végrehajtó biztonsági környezetet.

A natívan lefordított tárolt eljárások esetében nincs korlátozás a EXECUTE AS záradékra. Az SQL Server 2014 (12.x) és korábbi verzióiban a SELF, OWNER, és a "user_name" záradékok natívan lefordított tárolt eljárásokkal támogatottak.

További információ: EXECUTE AS záradék (Transact-SQL).

REPLIKÁCIÓHOZ

Alkalmazható: SQL Server 2008 (10.0.x) és újabb verziók, Azure SQL Database, SQL adatbázis Microsoft Fabric-ben.

Megadja, hogy a replikációhoz létrejön-e az eljárás. Következésképpen nem hajtható végre az előfizetőn. A FOR REPLICATION beállítással létrehozott eljárásokat a rendszer eljárásszűrőként használja, és csak a replikáció során hajtja végre. A paraméterek nem deklarálhatók, ha a REPLIKÁCIÓ PARAMÉTER meg van adva. A REPLIKÁCIÓ NEM adható meg a CLR-eljárásokhoz. A FOR REPLICATION szolgáltatással létrehozott eljárások esetében a RENDSZER figyelmen kívül hagyja a RECOMPILE beállítást.

Az FOR REPLICATION eljárásban rf típusú objektum található.sys.objectssys.procedures

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

Egy vagy több Transact-SQL utasítás, amely az eljárás törzsét tartalmazza. Az opcionális BEGIN és END kulcsszavak használatával csatolhatja az utasításokat. További információkért tekintse meg az alábbi ajánlott eljárásokat, általános megjegyzéseket, valamint korlátozásokat és korlátozásokat ismertető szakaszokat.

KÜLSŐ NÉV assembly_name. class_name. method_name

Alkalmazható: SQL Server 2008 (10.0.x) és újabb verziók, Azure SQL Database, SQL adatbázis Microsoft Fabric-ben.

Megadja a hivatkozni kívánt CLR-eljárás .NET-keretrendszer-szerelvényének metódusát. class_name érvényes SQL Server-azonosítónak kell lennie, és osztályként kell léteznie a szerelvényben. Ha az osztály névtér-minősített neve pont (.) használatával választja el a névtérrészeket, az osztály nevét szögletes zárójelek ([]) vagy idézőjelek ("") használatával kell elválasztani. A megadott metódusnak az osztály statikus metódusának kell lennie.

Alapértelmezés szerint az SQL Server nem tudja végrehajtani a CLR-kódot. Létrehozhat, módosíthat és elvethet olyan adatbázis-objektumokat, amelyek általános nyelvi futtatókörnyezeti modulokra hivatkoznak; Ezeket a hivatkozásokat azonban csak akkor hajthatja végre az SQL Serverben, ha engedélyezi a clr-kompatibilis beállítást. A beállítás engedélyezéséhez használja a sp_configure.

Megjegyzés:

A CLR-eljárások nem támogatottak a tárolt adatbázisokban.

ATOMI ÉS

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Atomi tárolt eljárás végrehajtását jelzi. A módosítások véglegesítése vagy az összes módosítás visszaállítása kivétel kiváltásával. A natívan lefordított tárolt eljárásokhoz az ATOMIC WITH blokk szükséges.

Ha az eljárás VISSZAÁLL (kifejezetten a RETURN utasításon keresztül, vagy implicit módon a végrehajtás befejezésével), az eljárás által elvégzett munka véglegesítve lesz. Ha az eljárás thROWs, az eljárás által elvégzett munka vissza lesz vonva.

XACT_ABORT alapértelmezés szerint be van kapcsolva egy atomblokkon belül, és nem módosítható. XACT_ABORT megadja, hogy az SQL Server automatikusan visszaállítja-e az aktuális tranzakciót, amikor egy Transact-SQL utasítás futásidejű hibát jelez.

A következő SET beállítások mindig be vannak kapcsolva az ATOMIC blokkban, és nem módosíthatók.

• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER, ARITHABORT
• NOCOUNT
• ANSI_NULLS
• ANSI_WARNINGS (ANSI figyelmeztetések)

A SET-beállítások nem módosíthatók az ATOMIC-blokkokban. A felhasználói munkamenet SET beállításai nem használhatók a natívan lefordított tárolt eljárások hatókörében. Ezek a beállítások fordításkor vannak javítva.

A BEGIN, a ROLLBACK és a COMMIT műveletek nem használhatók atomblokkon belül.

Natívan lefordított tárolt eljárásonként egy ATOMIC-blokk található az eljárás külső hatókörében. A blokkok nem ágyazhatók be. További információ az atomblokkokról: Natívan lefordított tárolt eljárások.

NULL | NEM NULL

Meghatározza, hogy a paraméterben engedélyezve vannak-e null értékek. A NULL az alapértelmezett érték.

NATIVE_COMPILATION

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Azt jelzi, hogy az eljárás natív módon van lefordítva. NATIVE_COMPILATION, SCHEMABINDING és EXECUTE AS bármilyen sorrendben megadható. További információ: Natívan lefordított tárolt eljárások.

SÉMAKÖTÉS

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Biztosítja, hogy az eljárás által hivatkozott táblákat ne lehessen elvetni vagy módosítani. A SCHEMABINDING natívan lefordított tárolt eljárásokhoz szükséges. (További információ: Natívan lefordított tárolt eljárások.) A SCHEMABINDING korlátozásai megegyeznek a felhasználó által definiált függvényekkel. További információt a CREATE FÜGGVÉNY (Transact-SQL) SCHEMABINDING szakaszában talál.

NYELV = [N] 'nyelv'

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Egyenértékű a SET LANGUAGE (Transact-SQL) munkamenet-beállítással. NYELV = [N] "nyelv" szükséges.

TRANZAKCIÓELKÜLÖNÍTÉSI SZINT

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Natívan lefordított tárolt eljárásokhoz szükséges. A tárolt eljárás tranzakcióelkülönítési szintjét adja meg. A lehetőségek a következők:

További információ ezekről a lehetőségekről: TRANZAKCIÓELKÜLÖNÍTÉSI SZINT beállítása (Transact-SQL).

ISMÉTELHETŐ OLVASÁS

Megadja, hogy az utasítások nem tudják olvasni azokat az adatokat, amelyeket módosítottak, de más tranzakciók még nem véglegesítettek. Ha egy másik tranzakció módosítja az aktuális tranzakció által beolvasott adatokat, az aktuális tranzakció meghiúsul.

SZERIALIZÁLHATÓ

A következőket adja meg:

• Az utasítások nem tudják olvasni azokat az adatokat, amelyeket módosítottak, de más tranzakciók még nem véglegesítettek.
• Ha egy másik tranzakció módosítja az aktuális tranzakció által beolvasott adatokat, az aktuális tranzakció meghiúsul.
• Ha egy másik tranzakció új sorokat szúr be olyan kulcsértékekkel, amelyek az aktuális tranzakció bármely utasítása által beolvasott kulcstartományba esnének, az aktuális tranzakció meghiúsul.

PILLANATFELVÉTEL

Azt adja meg, hogy a tranzakció bármely utasítása által beolvasott adatok a tranzakció elején létező adatok tranzakcióslag konzisztens verziója.

DATEFIRST = szám

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

A hét első napját adja meg egy 1 és 7 közötti számra. DATEFIRST nem kötelező. Ha nincs megadva, a beállítás a megadott nyelvből származik.

További információ: SET DATEFIRST (Transact-SQL).

DATEFORMAT = formátum

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Megadja a hónap, a nap és az év dátumrészeinek sorrendjét a dátum, a smalldatetime, a datetime, a datetime2 és a datetimeoffset karaktersztringek értelmezéséhez. A DATEFORMAT nem kötelező. Ha nincs megadva, a beállítás a megadott nyelvből származik.

További információ: SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { KI | ON }

Alkalmazható: SQL Server 2014 (12.x) és újabb verziók, Azure SQL Database, SQL database in Microsoft Fabric.

Az SQL Server-tranzakciók véglegesítése lehet teljesen tartós, alapértelmezett vagy késleltetett tartós.

További információ: Tranzakció tartósságának szabályozása.

Egyszerű példák

Az első lépésekhez íme két gyors példa: SELECT DB_NAME() AS ThisDB; az aktuális adatbázis nevét adja vissza. Ezt az utasítást egy tárolt eljárásban is becsomagolhatja, például:

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

Az áruház eljárásának meghívása utasítással: EXEC What_DB_is_this;

Valamivel összetettebb, ha egy bemeneti paramétert ad meg az eljárás rugalmasabbá tétele érdekében. Például:

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

Adja meg az adatbázis-azonosító számát, amikor meghívja az eljárást. Például EXEC What_DB_is_that 2;tempdbad vissza.

További példákért tekintse meg a cikk vége felé látható példákat.

Ajánlott eljárások

Bár ez nem az ajánlott eljárások teljes listája, ezek a javaslatok javíthatják az eljárás teljesítményét.

• Használja a SET NOCOUNT ON utasítást az eljárás törzsének első utasításaként. Vagyis helyezze el az AS kulcsszó után. Ez kikapcsolja azOKAT az üzeneteket, amelyeket az SQL Server a SELECT, INSERT, UPDATE, MERGE és DELETE utasítások végrehajtása után küld vissza az ügyfélnek. Ezzel a lehető legkisebbre állítja a létrehozott kimenetet az egyértelműség érdekében. A mai hardveren azonban nincs mérhető teljesítménybeli előny. További információ: SET NOCOUNT (Transact-SQL).
• Használjon sémaneveket adatbázis-objektumok létrehozásakor vagy hivatkozásakor az eljárásban. Az adatbázismotor kevesebb feldolgozási időt vesz igénybe az objektumnevek feloldásához, ha nem kell több sémában keresnie. Emellett megakadályozza a felhasználók alapértelmezett sémájának hozzárendelése által okozott engedély- és hozzáférési problémákat is, amikor az objektumok a séma megadása nélkül jönnek létre.
• Kerülje a WHERE és a JOIN záradékban megadott oszlopok körüli körbefuttatási függvényeket. Ez nem determinisztikussá teszi az oszlopokat, és megakadályozza, hogy a lekérdezésfeldolgozó indexeket használ.
• Kerülje a skaláris függvények használatát a SELECT utasításokban, amelyek sok adatsort adnak vissza. Mivel a skaláris függvényt minden sorra alkalmazni kell, az eredményül kapott viselkedés olyan, mint a soralapú feldolgozás, és csökkenti a teljesítményt.
• Kerülje a .SELECT * Ehelyett adja meg a szükséges oszlopneveket. Ez megakadályozhatja, hogy az adatbázismotor bizonyos hibái leállítsák az eljárás végrehajtását. Például egy SELECT * utasítás, amely adatokat ad vissza egy 12 oszlopos táblából, majd beszúrja az adatokat egy 12 oszlopból álló ideiglenes táblába, mindaddig sikeres lesz, amíg a két tábla oszlopainak száma vagy sorrendje nem változik.
• Kerülje a túl sok adat feldolgozását vagy visszaadását. Az eljáráskódban a lehető leghamarabb szűkítse le az eredményeket, hogy az eljárás által végrehajtott további műveletek a lehető legkisebb adatkészlettel legyenek végrehajtva. Csak az alapvető adatokat küldje el az ügyfélalkalmazásnak. Hatékonyabb, mint extra adatokat küldeni a hálózaton, és arra kényszeríti az ügyfélalkalmazást, hogy szükségtelenül nagy eredményhalmazokon keresztül működjön.
• Használjon explicit tranzakciókat a BEGIN/COMMIT TRANZAKCIÓ használatával, és tartsa a lehető legrövidebb ideig a tranzakciókat. A hosszabb tranzakciók hosszabb rekordzárolást és nagyobb holtpontot jelentenek.
• Használja a Transact-SQL TRY... CATCH funkció az eljáráson belüli hibakezeléshez. MEGPRÓBÁL... A CATCH Transact-SQL utasítások teljes blokkját beágyazhatja. Ez nem csak kisebb teljesítményterhelést eredményez, hanem a hibajelentést is pontosabbá teszi, lényegesen kevesebb programozással.
• Használja a DEFAULT kulcsszót az eljárás törzsében található CREATE TABLE vagy ALTER TABLE Transact-SQL utasítások által hivatkozott összes táblaoszlopon. Ez megakadályozza, hogy a NULL értéket olyan oszlopoknak adja át, amelyek nem engedélyezik a null értékeket.
• Használjon NULL vagy NOT NULL értéket egy ideiglenes tábla minden oszlopához. A ANSI_DFLT_ON és a ANSI_DFLT_OFF beállításai szabályozzák, hogy az adatbázismotor hogyan rendeli hozzá a NULL vagy NOT NULL attribútumokat az oszlopokhoz, ha ezek az attribútumok nincsenek megadva a CREATE TABLE vagy AZ ALTER TABLE utasításban. Ha egy kapcsolat más beállításokat tartalmazó eljárást hajt végre, mint az eljárást létrehozó kapcsolat, a második kapcsolathoz létrehozott tábla oszlopai eltérő null értékűek lehetnek, és eltérő viselkedést mutathatnak. Ha a NULL vagy a NOT NULL explicit módon van megadva az egyes oszlopokhoz, az ideiglenes táblákat ugyanazzal a nullhiba-értékkel hozza létre a rendszer az eljárást végrehajtó összes kapcsolat esetében.
• Null értékeket konvertáló módosító utasítások használata, amelyek olyan logikát tartalmaznak, amely kizárja a lekérdezésekből null értékű sorokat. Vegye figyelembe, hogy a Transact-SQL-ben a NULL nem üres vagy "semmi" érték. Ismeretlen érték helyőrzője, és váratlan viselkedést okozhat, különösen eredményhalmazok lekérdezésekor vagy AZGGREGÁTUM függvények használatakor.
• Használja az UNION ALL operátort az UNION vagy OR operátorok helyett, kivéve, ha külön értékekre van szükség. Az UNION ALL operátor kevesebb feldolgozási többletterhelést igényel, mert az ismétlődések nem szűrhetők ki az eredményhalmazból.

Megjegyzések

Az eljárásnak nincs előre definiált maximális mérete.

Az eljárásban megadott változók lehetnek felhasználó által definiált vagy rendszerváltozók, például @@SPID.

Amikor egy eljárás első alkalommal kerül végrehajtásra, a rendszer úgy állítja össze, hogy meghatározza az adatok lekéréséhez szükséges optimális hozzáférési tervet. Az eljárás későbbi végrehajtása újra felhasználhatja a már létrehozott tervet, ha továbbra is az adatbázismotor tervgyorsítótárában marad.

Egy vagy több eljárás automatikusan végrehajtható az SQL Server indításakor. Az eljárásokat a rendszergazdának kell létrehoznia az master adatbázisban, és háttérfolyamatként a sysadmin rögzített kiszolgálói szerepkörrel kell végrehajtania. Az eljárások nem tartalmazhatnak bemeneti vagy kimeneti paramétereket. További információ: Tárolt eljárás végrehajtása.

Az eljárások beágyazottak, amikor az egyik eljárás meghív egy másikat, vagy egy CLR-rutinra, típusra vagy összesítésre hivatkozva végrehajtja a felügyelt kódot. Az eljárások és a felügyelt kódhivatkozások legfeljebb 32 szintbe ágyazhatók. A beágyazási szint eggyel nő, amikor a hívott eljárás vagy a felügyelt kód referenciája megkezdi a végrehajtást, és egyel csökken, amikor a hívott eljárás vagy a felügyelt kód referenciája befejezi a végrehajtást. A felügyelt kódból meghívott metódusok nem számítanak bele a beágyazási szint korlátjába. Ha azonban egy CLR által tárolt eljárás adathozzáférési műveleteket hajt végre az SQL Server felügyelt szolgáltatón keresztül, a rendszer további beágyazási szintet ad hozzá a felügyelt kódról az SQL-re való áttéréshez.

Ha megkísérli túllépni a maximális beágyazási szintet, a teljes híváslánc meghiúsul. A @@NESTLEVEL függvénnyel visszaadhatja az aktuális tárolt eljárás végrehajtásának beágyazási szintjét.

Interoperability

Az adatbázismotor a SET QUOTED_IDENTIFIER és a SET ANSI_NULLS beállításait is menti Transact-SQL eljárás létrehozásakor vagy módosításakor. Ezeket az eredeti beállításokat az eljárás végrehajtásakor használja a rendszer. Ezért a SET QUOTED_IDENTIFIER és a SET ANSI_NULLS ügyfél-munkamenetbeállításai figyelmen kívül lesznek hagyva az eljárás futtatásakor.

Más SET-beállítások, például a SET ARITHABORT, a SET ANSI_WARNINGS vagy a SET ANSI_PADDINGS nem lesznek mentve egy eljárás létrehozásakor vagy módosításakor. Ha az eljárás logikája egy adott beállítástól függ, az eljárás elején adjon meg egy SET utasítást a megfelelő beállítás garantálása érdekében. Ha egy SET utasítást egy eljárásból hajt végre, a beállítás csak addig marad érvényben, amíg az eljárás le nem fut. Ezt követően a rendszer visszaállítja a beállítást arra az értékre, amely az eljárás meghívásakor volt. Így az egyes ügyfelek az eljárás logikájának befolyásolása nélkül állíthatják be a kívánt beállításokat.

Bármely SET utasítás megadható egy eljáráson belül, kivéve a SET SHOWPLAN_TEXT és a SET SHOWPLAN_ALL. Csak ezek az utasítások lehetnek a kötegben. A kiválasztott SET beállítás az eljárás végrehajtása során is érvényben marad, majd visszaáll a korábbi beállításra.

Megjegyzés:

A SET ANSI_WARNINGS nem érvényes, ha paramétereket ad át egy eljárásban, felhasználó által definiált függvényben, vagy amikor változókat deklarál és beállít egy kötegelt utasításban. Ha például egy változó karakter(3) értékként van definiálva, majd három karakternél nagyobb értékre van állítva, az adatok csonkulnak a megadott méretre, és az INSERT vagy AZ UPDATE utasítás sikeres lesz.

Korlátozások és korlátozások

A CREATE PROCEDURE utasítás nem kombinálható egyetlen köteg más Transact-SQL utasításaival.

A következő utasítások nem használhatók a tárolt eljárás törzsében.

CREATE	SET	USE
ÖSSZESÍTÉS LÉTREHOZÁSA	FELKÉSZÜLÉS SHOWPLAN_TEXT	HASZNÁLD database_name
LÉTREHOZÁS ALAPÉRTELMEZETTKÉNT	SHOWPLAN_XML BEÁLLÍTÁSA
SZABÁLY LÉTREHOZÁSA	PARSEONLY BEÁLLÍTÁSA
SÉMA LÉTREHOZÁSA	SHOWPLAN_ALL BEÁLLÍTÁSA
CREATE vagy ALTER TRIGGER
CREATE vagy ALTER FÜGGVÉNY
LÉTREHOZÁSI VAGY MÓDOSÍTÁSI ELJÁRÁS
LÉTREHOZÁS VAGY NÉZET MÓDOSÍTÁSA

Egy eljárás hivatkozhat olyan táblákra, amelyek még nem léteznek. Létrehozáskor csak a szintaxis ellenőrzése történik. Az eljárás csak az első alkalommal lesz lefordítva. Az eljárásban csak a fordítás során hivatkoznak az összes objektumra. Ezért a nem létező táblákra hivatkozó szintaktikailag helyes eljárás sikeresen létrehozható; Az eljárás azonban a végrehajtási időpontban meghiúsul, ha a hivatkozott táblák nem léteznek.

A függvény nevét nem adhatja meg alapértelmezett paraméterértékként, illetve az eljárás végrehajtásakor a paraméternek átadott értékként. A függvényeket azonban változóként is átadhatja az alábbi példában látható módon.

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

Ha az eljárás az SQL Server távoli példányán végez módosításokat, a módosítások nem állíthatók vissza. A távoli eljárások nem vesznek részt a tranzakciókban.

Ahhoz, hogy az adatbázismotor a .NET-keretrendszerben túlterhelt megfelelő metódusra hivatkozzon, a KÜLSŐ NÉV záradékban megadott metódusnak a következő jellemzőkkel kell rendelkeznie:

• Deklarálandó statikus módszerként.
• A paraméterek száma megegyezik az eljárás paramétereinek számával.
• Olyan paramétertípusokat használjon, amelyek kompatibilisek az SQL Server-eljárás megfelelő paramétereinek adattípusaival. Az SQL Server adattípusainak a .NET-keretrendszer adattípusokkal való egyeztetéséről további információt a CLR paraméteradatok leképezése című témakörben talál.

Metadaták

Az alábbi táblázat felsorolja a katalógusnézeteket és a dinamikus felügyeleti nézeteket, amelyekkel adatokat adhat vissza a tárolt eljárásokról.

View	Description
sys.sql_modules	Egy Transact-SQL eljárás definícióját adja vissza. A ENCRYPTION beállítással létrehozott eljárás szövege nem tekinthető meg a sys.sql_modules katalógusnézettel.
sys.assembly_modules	ClR-eljárással kapcsolatos információkat ad vissza.
sys.parameters	Az eljárásban meghatározott paraméterekkel kapcsolatos információkat adja vissza
sys.sql_expression_dependenciessys.dm_sql_referenced_entitiessys.dm_sql_referencing_entities	Az eljárás által hivatkozott objektumokat adja vissza.

Egy lefordított eljárás méretének becsléséhez használja a következő Teljesítményfigyelő számlálókat.

Teljesítményfigyelő objektum neve	Teljesítményfigyelő számláló neve
SQLServer: Gyorsítótár-objektum megtervezése	Gyorsítótár találati aránya
	Gyorsítótár lapjai
	Gyorsítótárobjektumok száma 1

¹ Ezek a számlálók a gyorsítótárobjektumok különböző kategóriáihoz érhetők el, beleértve az alkalmi Transact-SQL-t, az előkészített Transact-SQL-t, az eljárásokat, az eseményindítókat stb. További információ: SQL Server, Plan Cache Object.

Permissions

Engedélyre van szükség CREATE PROCEDURE az adatbázisban, és ALTER engedélyre van szükség azon a sémán, amelyben az eljárás létrejön, vagy tagságot igényel a db_ddladmin rögzített adatbázis-szerepkörben.

A CLR által tárolt eljárásokhoz a KÜLSŐ NÉV záradékban hivatkozott szerelvény tulajdonjogát vagy REFERENCES az adott szerelvény engedélyét kell megadni.

CREATE PROCEDURE és memóriaoptimalizált táblák

A memóriaoptimalizált táblák a hagyományos és natív módon lefordított tárolt eljárásokon keresztül is elérhetők. A natív eljárások a legtöbb esetben hatékonyabbak. További információ: Natívan lefordított tárolt eljárások.

Az alábbi minta bemutatja, hogyan hozhat létre natívan lefordított tárolt eljárást, amely hozzáfér egy memóriaoptimalizált táblához dbo.Departments:

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

A NATIVE_COMPILATION nélkül létrehozott eljárás nem módosítható natívan lefordított tárolt eljárásra.

A natívan lefordított tárolt eljárások, a támogatott lekérdezési felület és az operátorok programozhatóságáról a Natívan lefordított T-SQL-modulok támogatott funkciói című témakörben olvashat.

Példák

Kategória	Kiemelt szintaxiselemek
Alapszintű szintaxis	AZ ELJÁRÁS LÉTREHOZÁSA
Paraméterek átadása	@parameter = alapértelmezett HOZAM táblaértékkel megadott paraméter típusa A KURZOR VÁLTOZÓ
Adatok módosítása tárolt eljárással	Frissítés
Hibakezelés	TRY...CATCH
Az eljárásdefiníció elrejtése	TITKOSÍTÁSSAL
Az eljárás újrafordításának kényszerítése	ÚJRAFORDÍTÁSSAL
A biztonsági környezet beállítása	VÉGREHAJTÁS MÁSKÉNT

Alapszintű szintaxis

Az ebben a szakaszban található példák a CREATE PROCEDURE utasítás alapvető funkcióit mutatják be a minimálisan szükséges szintaxis használatával.

A. Transact-SQL-eljárás létrehozása

Az alábbi példa egy tárolt eljárást hoz létre, amely visszaadja az összes alkalmazottat (a megadott kereszt- és vezetékneveket), a munkaköri címeiket és az osztályneveiket az AdventureWorks2025 adatbázisban található nézetből. Ez az eljárás nem használ paramétereket. A példa ezután az eljárás végrehajtásának három módszerét mutatja be.

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

SELECT * FROM HumanResources.vEmployeeDepartment;

Az uspGetEmployees eljárás a következő módokon hajtható végre:

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

B. Több eredményhalmaz visszaadása

Az alábbi eljárás két eredményhalmazt ad vissza.

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

C. CLR-ben tárolt eljárás létrehozása

Az alábbi példa létrehozza azt az GetPhotoFromDB eljárást, amely az GetPhotoFromDB osztály metódusára LargeObjectBinary hivatkozik a HandlingLOBUsingCLR szerelvényben. Az eljárás létrehozása előtt a rendszer regisztrálja a HandlingLOBUsingCLR szerelvényt a helyi adatbázisban. A példa egy assembly_bits létrehozott szerelvényt feltételez.

Alkalmazható: SQL Server 2008 (10.0.x) és újabb verziók, Azure SQL Database, SQL adatbázis Microsoft Fabric-ben, amikor assembly_bits-ből létrehozott összeszerelést használunk.

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

Paraméterek átadása

Az ebben a szakaszban szereplő példák bemutatják, hogyan adhat át értékeket egy tárolt eljárásnak bemeneti és kimeneti paraméterekkel.

D. Eljárás létrehozása bemeneti paraméterekkel

Az alábbi példa egy tárolt eljárást hoz létre, amely egy adott alkalmazott adatait adja vissza az alkalmazott utónevének és vezetéknevének értékeinek átadásával. Ez az eljárás csak az átadott paraméterek pontos egyezéseit fogadja el.

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

Az uspGetEmployees eljárás a következő módokon hajtható végre:

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. Eljárás használata helyettesítő karakterparaméterekkel

Az alábbi példa egy tárolt eljárást hoz létre, amely az alkalmazottak adatait adja vissza úgy, hogy teljes vagy részleges értékeket ad át az alkalmazott utónevének és vezetéknevének. Ez az eljárásminta megfelel az átadott paramétereknek, vagy ha nem adja meg, az előre beállított alapértelmezett értéket használja (a betűvel Dkezdődő vezetékneveket).

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;

Az uspGetEmployees2 eljárás számos kombinációban végrehajtható. Itt csak néhány lehetséges kombináció látható.

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. OUTPUT-paraméterek használata

Az alábbi példa létrehozza az uspGetList eljárást. Ez az eljárás azoknak a termékeknek a listáját adja vissza, amelyek árai nem lépik túl a megadott mennyiséget. A példa több SELECT utasítás és több OUTPUT paraméter használatát mutatja be. A KIMENETI paraméterek lehetővé teszik egy külső eljárás, köteg vagy több Transact-SQL utasítás elérését az eljárás végrehajtása során beállított értékek eléréséhez.

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

Hajtsa végre uspGetList , hogy visszaadja az Adventure Works termékek (Kerékpárok) listáját, amelyek kevesebbe kerülnek, mint $700. A OUTPUT paraméterek, @Cost és @ComparePrices a folyamatvezérlő nyelv használatával küldik vissza az üzeneteket az Üzenetek ablakban.

Megjegyzés:

A KIMENETI változót az eljárás létrehozásakor és a változó használatakor is meg kell határozni. A paraméter nevének és a változó nevének nem kell egyeznie; az adattípusnak és a paraméterhelyezésnek azonban meg kell egyeznie, kivéve, ha @ListPrice = változót használ.

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

Íme a részleges eredményhalmaz:

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. Táblaértékkel rendelkező paraméter használata

Az alábbi példa egy táblaértékű paramétertípussal több sort szúr be egy táblába. A példa létrehozza a paramétertípust, deklarál egy táblaváltozót, hogy hivatkozzon rá, kitölti a paraméterlistát, majd átadja az értékeket egy tárolt eljárásnak. A tárolt eljárás az értékekkel több sort szúr be egy táblába.

/* 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. OUTPUT kurzorparaméter használata

Az alábbi példa a OUTPUT kurzorparaméter használatával a helyi kurzort adja át egy eljárásnak a hívási kötegnek, eljárásnak vagy eseményindítónak.

Először hozza létre a deklarált eljárást, majd nyissa meg a kurzort a Currency táblában:

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

Ezután futtasson egy köteget, amely deklarál egy helyi kurzorváltozót, végrehajtja a kurzor helyi változóhoz rendelésének eljárását, majd lekéri a sorokat a kurzorból.

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

Adatok módosítása tárolt eljárással

Az ebben a szakaszban szereplő példák bemutatják, hogyan szúrhat be vagy módosíthat adatokat táblákban vagy nézetekben úgy, hogy egy adatmanipulációs nyelv (DML) utasítást is belefoglal az eljárás definíciójába.

I. Az UPDATE használata tárolt eljárásban

Az alábbi példa egy UPDATE utasítást használ egy tárolt eljárásban. Az eljárás egy bemeneti paramétert és @NewHours egy kimeneti paramétert @RowCountvesz igénybe. A @NewHours paraméter értéke az UPDATE utasításban a tábla VacationHoursoszlopának HumanResources.Employee frissítésére szolgál. A @RowCount kimeneti paraméter a helyi változóra érintett sorok számát adja vissza. A SET záradék egy CASE kifejezést használ a beállított érték feltételes meghatározásához VacationHours. Ha az alkalmazottat óránként fizetik (SalariedFlag = 0), VacationHours akkor a rendszer az aktuális óraszámot és a megadott @NewHoursértéket állítja be; VacationHours ellenkező esetben a megadott @NewHoursértékre van állítva.

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;

Hibakezelés

Az ebben a szakaszban szereplő példák a tárolt eljárás végrehajtásakor esetlegesen előforduló hibák kezelésére szolgáló módszereket mutatnak be.

J. A TRY használata... ELKAP

Az alábbi példa a TRY használatával... CATCH szerkezet a tárolt eljárás végrehajtása során észlelt hibainformációk visszaadásához.

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;

Az eljárásdefiníció elrejtése

Az ebben a szakaszban szereplő példák bemutatják, hogyan rejtheti el a tárolt eljárás definícióját.

K. A WITH ENCRYPTION beállítás használata

Az alábbi példa létrehozza az HumanResources.uspEncryptThis eljárást.

Alkalmazható: SQL Server 2008 (10.0.x) és újabb verziók, Azure SQL Database, SQL adatbázis Microsoft Fabric-ben.

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

A WITH ENCRYPTION beállítás elhomályosítja az eljárás definícióját a rendszerkatalógus lekérdezése vagy metaadatfüggvények használata során, ahogyan azt az alábbi példák is szemléltetik.

Futtatás sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Itt van az eredmények összessége.

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

A katalógusnézet közvetlen lekérdezése sys.sql_modules :

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

Itt van az eredmények összessége.

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

Megjegyzés:

Az Azure Synapse Analytics nem támogatja a rendszer által tárolt eljárást sp_helptext . Ehelyett használja az sys.sql_modules objektumkatalógus nézetet.

Az eljárás újrafordításának kényszerítése

Az ebben a szakaszban szereplő példák a WITH RECOMPILE záradék használatával kényszerítik az eljárást az újrafordításra minden egyes végrehajtásakor.

L. A WITH RECOMPILE (ÚJRAFORDÍTÁS) lehetőség használata

A WITH RECOMPILE záradék akkor hasznos, ha az eljáráshoz megadott paraméterek nem jellemzőek, és ha egy új végrehajtási tervet nem szabad gyorsítótárazni vagy tárolni a memóriában.

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;

A biztonsági környezet beállítása

Az ebben a szakaszban szereplő példák az EXECUTE AS záradék használatával adhatók meg a tárolt eljárás végrehajtásának biztonsági környezetében.

M. Az EXECUTE AS záradék használata

Az alábbi példa az EXECUTE AS záradék használatával adja meg azt a biztonsági környezetet, amelyben egy eljárás végrehajtható. A példában a beállítás CALLER azt határozza meg, hogy az eljárást a meghívó felhasználó kontextusában lehet végrehajtani.

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. Egyéni engedélykészletek létrehozása

Az alábbi példa az EXECUTE AS használatával hoz létre egyéni engedélyeket egy adatbázis-művelethez. Egyes műveletek, például a TRUNCATE TABLE, nem rendelkeznek engedélyezhető engedélyekkel. Ha a TRUNCATE TABLE utasítást egy tárolt eljárásba építi be, és megadja, hogy az eljárás olyan felhasználóként legyen végrehajtva, aki rendelkezik a tábla módosítására vonatkozó engedélyekkel, kiterjesztheti az engedélyeket arra a felhasználóra, akinek az eljáráshoz VÉGREHAJTÁSI engedélyeket ad.

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

Példák: Azure Synapse Analytics and Analytics Platform System (PDW)

O. SELECT utasítást futtató tárolt eljárás létrehozása

Ez a példa egy eljárás létrehozásának és futtatásának alapvető szintaxisát mutatja be. Köteg futtatásakor a CREATE PROCEDURE utasításnak kell lennie az első utasításnak. Ha például a következő tárolt eljárást szeretné létrehozni az AdventureWorksPDW2022-ben, először állítsa be az adatbázis-környezetet, majd futtassa a CREATE PROCEDURE utasítást.

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

Lásd még

• MÓDOSÍTÁSI ELJÁRÁS (Transact-SQL)
• Áramlásvezérlési nyelv (Transact-SQL)
• Kurzorok
• Adattípusok (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• ELJÁRÁS ELTÁVOLÍTÁSA (Transact-SQL)
• EXECUTE (Transact-SQL)
• VÉGREHAJTÁS (Transact-SQL)
• tárolt eljárások (adatbázismotor)
• sp_procoption (Transact-SQL)
• sp_recompile (Transact-SQL)
• sys.sql_modulok (Transact-SQL)
• sys.parameters (Transact-SQL)
• Rendszerrendszeri eljárások (Transact-SQL)
• sys.sql_expression_dependencies (Transact-SQL)
• sys.assembly_modules (Transact-SQL)
• sys.numbered_procedures (Transact-SQL)
• sys.numbered_procedure_parameters (Transact-SQL)
• OBJECT_DEFINITION (Transact-SQL)
• Tárolt eljárás létrehozása
• Table-Valued paraméterek (adatbázismotor) használata
• sys.dm_sql_referenced_entities (Transact-SQL)
• sys.dm_sql_referencing_entities (Transact-SQL)