C# - MS SQL tárolt eljárások

MS SQL tárolt eljárások 1. rész

forráskód letöltése
A Microsoft SQL adatbázis-kezelővel korábban már több cikkben, cikksorozatban foglalkoztunk. Most egy újabb cikksorozatot indítunk, amelyben az MS SQL tárolt eljárásaival ismerkedhetünk meg. Az első részben olyan tárolt eljárásokat ismertetünk, amelyekkel az adatbázisról, az adatbázis tárol eljárásairól, tábláiról, valamint az SQL Server adatbázisairól kérdezhetünk le információkat.
A tárolt eljárások az SQL szerver adatbázisában tárolt Transact-SQl utasításhalmazok. Ezek végrehajtása a normál SQL lekérdezésekkel gyorsabb, mivel az első futtatáskor lefordításra kerülnek, és a lefordított kód kerül tárolásra az adatbázis rendszertáblájában. A fordítás ráadásul több szempontot figyelembe vevő optimalizálással történik, így ezek a teljesítményt hatékonyan növelik.
01_sp_databases.sql: Az SQL szerver adatbázisainak lekérdezése
Elsőként mindjárt egy olyan eljárással kezdjük, amellyel az SQL szerver adatbázisainak listáját kérdezhetjük le. Ez az eljárás az sp_databases, aminek használata igen egyszerű, ugyanis nincsenek paraméterei. Egy eredménylistát ad az alábbi formátummal:
Mező Típus Leírás
DATABASE_NAME sysname az adatbázis neve a sysdatabases rendszertáblából.
DATABASE_SIZE int az adatbázis mérete kB-ban
REMARKS varchar(254) ebben a mezőben mindig NULL értéket kapunk.
02_sp_stored_procedures.sql: Adatbázis tárolt eljárásainak lekérdezése
Egy adatbázis tárolt eljárásainak lekérdezéséhez az sp_stored_procedures eljárást használhatjuk. Ennek szintaxisa a következő:
sp_stored_procedures [[@sp_name =] 'name'] 
    [,[@sp_owner =] 'owner'] 
Ha csak egyetlen tárolt eljárás adataira vagyunk kíváncsiak, akkor annak nevét az sp_name paraméterben adhatjuk meg. Megadhatunk egy maszkot is, ekkor azok a tárolt eljárások lesznek listázva, amelyekre illeszkedik a maszk.
03_sp_sproc_columns.sql: Tárolt eljárás eredménylistájának szerkezete
Egy tárolt eljárás általában egy adatkészletet ad vissza. Ennek az adatkészletnek a szerkezetét kérdezhetjük le az sp_sproc_columns tárolt eljárással:
sp_sproc_columns [[@procedure_name =] 'name'] 
    [,[@procedure_owner =] 'owner'] 
    [,[@procedure_qualifier =] 'qualifier'] 
    [,[@column_name =] 'column_name']
    [,[@ODBCVer =] 'ODBCVer']
Ha egy konkrét eljárás eredménylistájának szerkezetére vagyunk kíváncsiak, akkor a procedure_name paraméterben megadhatjuk annak nevét, vagy egy maszkot, aminek illeszkednie kell az eljárás vagy eljárások nevére. Ha csak egy oszlop adataira vagyunk kíváncsiak, akkor annak nevét a procedure_qualifier paraméterben adhatjuk meg.
04_sp_tables_1.sql: Egy adatbázis tábláinak lekérdezése
Egy adott adatbázis tábláinak adatait az sp_tables tárolt eljárással kérdezhetjük le. Az eljárás szintaktikája a következő:
sp_tables [ [ @table_name = ] 'name' ] 
    [ , [ @table_owner = ] 'owner' ] 
    [ , [ @table_qualifier = ] 'qualifier' ] 
    [ , [ @table_type = ] "type" ]
Ha egy konkrét táblára vagyunk csak kíváncsiak, akkor annak nevét a table_name paraméterben adhatjuk meg. Itt is van lehetőség egy keresési maszk megadására. A table_type paraméter a tábla típusának meghatározására szolgál. Ha megadjuk, akkor csak az adott típusú táblák adatai jelennek meg az eredménylistában. Értékei a következők lehetnek: „TABLE” (normál táblák), „SYSTEm TABLE” (rendszertáblák), „VIEW” (nézettáblák). A paraméter sztringben egyszerre többet is megadhatunk ezekből, de ekkor a paramétert idézőjelek közé kell tenni, míg az egyes értékeket aposztróf közé, és vesszővel kell elválasztani őket egymástól:
use NorthWind;
exec sp_tables @table_type="'TABLE', 'SYSTEM TABLE'"
06_sp_columns_1.sql: Tábla oszlopainak lekérdezése
Egy adott tábla vagy nézet oszlopainak lekérdezésére az sp_columns tárolt eljárás szolgál.
sp_columns [ @table_name = ] object 
    [ , [ @table_owner = ] owner ] 
    [ , [ @table_qualifier = ] qualifier ] 
    [ , [ @column_name = ] column ] 
    [ , [ @ODBCVer = ] ODBCVer ]
A table_name paramétert kötelező megadni. Ebben a paraméterben kell megadni a tábla vagy a nézet nevét. Mivel keresési maszkot itt is megadhatunk, ezért lehetőség van az összes tábla összes mezőjének egyszerre történő lekérdezésére:
use NorthWind;
exec sp_columns @table_name='%'
Ha csak egy adott oszlop adataira vagyunk kíváncsiak, akkor annak nevét a column_name paraméterben adhatjuk meg. Itt is használhatunk keresési maszkot.
08_sp_special_columns.sql: Tábla speciális mezőinek lekérdezése
Lehetőség van a tábla speciális (kulcs-) mezőinek lekérdezésére. Erre az sp_special_columns tárolt eljárást használhatjuk.
sp_special_columns [@name =] 'name' 
    [,[owner =] 'owner'] 
    [,[@qualifier =] 'qualifier'] 
    [,[@col_type =] 'col_type'] 
    [,[@scope =] 'scope']
    [,[@nullable =] 'nullable'] 
    [,[@ODBCVer =] 'ODBCVer']
A name paraméterben kell megadnunk a tábla nevét, itt viszont már nem használhatunk maszkot. A col_type paraméterben egy karaktert adhatunk meg, ami meghatározza a lekérdezett oszlopok típusát. A megadható karakterek a következők:
Karakter Jelentés
R Csak az elsődleges kulcsban szereplő oszlopok adatait listázza
V Azokat az oszlopokat is listázza, amelyek értéke automatikusan kapja meg az értéket frissítéskor

MS SQL tárolt eljárások cikksorozat