2008.04_SQL Server 2005_[Aplikacje Biznesowe].pdf

Aplikacje

biznesowe

Artur Mościcki

SQL Server 2005

A plikacje biznesowe charakteryzują się dużą

złożonością. Aby w prosty (i niewymagający

zbyt dużych nakładów pracy) sposób rozwią-

zać napotykane zadania biznesowe, bardzo często

trzeba decydować się na użycie dynamicznego kodu

SQL. Jednak trzeba zdać sobie sprawę, że dynamicz-

ny kod SQL jest również źródłem dużego niebezpie-

czeństwa ze strony hakerów (o tym przeczytasz w dal-

szej części tego artykułu). W aplikacjach biznesowych

nie można sobie pozwolić na ryzyko „wypłynięcia” lub

„utraty” danych – dobrze napisane aplikacje stają się

źródłem sukcesu firmy, źle napisane mogą stać się

przyczyną klęski. Dlatego należy minimalizować ryzy-

ko związane z używaniem dynamicznego kodu SQL.

Z drugiej jednak strony, mądrze używany dynamiczny

kod SQL może przyspieszyć i ułatwić naszą pracę.

Składnia poleceń EXEC i sp _ executesql została

przedstawiona na Listingu 1.

Polecenie EXEC

Działanie polecenie EXEC najłatwiej zaprezentować na

przykładzie. Kod źródłowy na Listingu 2 zwraca liczbę

wierszy z tabeli o podanej nazwie.

W kodzie zaprezentowanym na Listingu 2 najpierw

zadeklarowane zostały zmienne tekstowe (nazwa sche-

matu, nazwa tabeli i obiekt – łączący schemat i tabelę

w całość), a następnie przypisano im wartości za po-

mocą instrukcji SET . Ostatnim krokiem jest użycie po-

lecenia EXEC . W kodzie tym kilkukrotnie pojawia się li-

tera N poprzedzająca łańcuch tekstowy. Litera ta jest

wymagana, jeśli używamy typów NVARCHAR , NCHAR , czy-

li obsługujących narodowe znaki. Należy także zwrócić

uwagę na fakt, że wewnątrz nawiasów instrukcji EXEC

dopuszczane jest użycie tylko zmiennych typów łań-

cuchowych i znakowych – nie można używać funkcji,

czy też wyrażenia CASE rzutującego na typ łańcuchowy.

Tak więc, wewnątrz nawiasów instrukcji EXEC nie moż-

na umieścić użytej przez nas w prezentowanym przy-

kładzie funkcji QUOTENAME , która powoduje ujęcie nazwy

obiektów w znaki cudzysłowów.

Jednak w przypadku, gdybyś chciał przypisać wy-

nik polecenia EXEC do zmiennej, to musisz wykorzystać

pewien trik – musisz posłużyć się tabelą. Odpowiedni

kod został zaprezentowany na Listingu 3.

Oczywiście instrukcja INSERT INTO może być ele-

mentem bloku poleceń wykonywanych przez EXEC .

W przykładach zaprezentowanych do tej pory uży-

waliśmy bardzo często konkatenacji. Taki proces ma

negatywny wpływ na efektywność kodu – co w przy-

padku aplikacji biznesowych jest niezmiernie istotne.

Z tego też względu kod dynamiczny powinien być uży-

wany z rozwagą. Przyjrzyjmy się wpływowi konkatena-

cji na plany wykonania zapytań SQL. Usuńmy najpierw

plany wykonania zapytania używając polecenia

Informacje ogólne

Na czym polega fenomen i główne zagrożenie zwią-

zane z dynamicznym kodem SQL. Otóż, chodzi o da-

nie użytkownikowi możliwości budowania fragmentów

zapytań. Użycie dynamicznego kodu SQL, w którym

fragmenty uzupełnia użytkownik pozwala na dużą in-

terakcję i nie wymaga wymyślania skomplikowanych

metod konfiguracji naszej aplikacji. Ale z drugiej stro-

ny, jest to po prostu pozwolenie użytkownikowi na pi-

sanie w naszym kodzie i jest rzeczą wyjątkowo skom-

plikowaną, aby zrobić to w sposób bezpieczny.

W SQL Server 2005 znajdują się dwa polecenia

pozwalające wykonywać dynamiczny kod SQL:

• EXEC lub EXECUTE ,

• sp _ executesql .

Preferowane jest używanie polecenia sp _ executesql ,

gdyż polecenie to zwiększa szansę na wykorzystanie

planów wykonania w przyszłości (zwiększa efektyw-

ność aplikacji biznesowych) oraz pozwala na tworze-

nie bezpieczniejszego kodu (ale o tym napiszę w dal-

szej części artykułu).

DBCC FREEPROCCACHE

Artur Mościcki jest z wykształcenia informatykiem. Obecnie

pracuje jako programista baz danych i hurtowni danych. Ma

również doświadczenie w tworzeniu aplikacji BI dla dużych

i średnich firm. Jest współautorem książek: Oracle 10g i

Delphi. Programowanie baz danych oraz Photoshop. Plugi-

ny i efekty specjalne. Oprócz hurtowni i baz danych, jego

drugą informatyczną pasją jest fotografia cyfrowa i obróbka

zdjęć za pomocą Adobe Photoshop. W wolnych chwilach

kibicuje piłkarskiej reprezentacji Argentyny.

Kontakt z autorem: arturmoscicki@op.pl

Następnie trzykrotnie wykonajmy kod zaprezentowa-

ny na Listingu 4 za każdym razem zmieniając wartość

zmiennej @p _ pensja .

Po wykonaniu bloku z Listingu 4 możemy wykonać

kod pokazujący nam plany zapytania związane z na-

szym zapytaniem.

SELECT objtype, cacheobjtype, usecounts, sql

FROM sys.syscacheobjects

WHERE sql NOT LIKE '%sys.%' AND sql NOT LIKE '%cache%'

ORDER BY objtype

www.sdjournal.org

Software Developer’s Journal 4/2008

SQL Server 2005

Polecenie EXEC

EXEC sp_addlinkedserver [nazwa, która będzie używana],

[instancja SQL Server 2005, do której

będziemy się łączyć]

Polecenie EXEC ma dwa zastosowania: dynamiczne wywoływanie pole-

ceń SQL i wywoływanie procedur. W tym artykule jest mowa o pierw-

szym zastosowaniu.

Po utworzeniu serwera połączonego (w moim przypadku COMP\

SQL _ SERVER ) można wydać polecenie, które zwróci pracowni-

ków zarabiających ponad 2000.

Jak się okazuje powstały cztery różne plany wykonania, dla każ-

dej wpisanej przez nas wartości powstał oddzielny plan oraz je-

den sparametryzowany plan wykonania (Rysunek 1). Plany zapy-

tania przedstawione w wierszach 2-4 na Rysunku 1 zostały stwo-

rzone po to, by w przyszłości móc z nich skorzystać w przypadku

wywołania z identyczną wartością parametru @p _ pensja .

W SQL Server 2005 jednak zdecydowanie zwiększono moż-

liwości oferowane przez dynamiczny kod SQL. W SQL Server

2000 wielkość łańcucha znakowego wykonywanego przez po-

lecenie EXEC nie mogła przekraczać 8000 znaków dla typu VAR-

CHAR lub 4000 znaków dla typu NVARCHAR , co w przypadku apli-

kacji biznesowych było dużym ograniczeniem. W SQL Server

2005 wejściowy łańcuch znakowy może mieć wielkość 2 GB,

czyli ponad 2 miliardy znaków typu VARCHAR i ponad miliard zna-

ków typu NVARCHAR . Zaprezentowany na Listingu 5 kod generu-

je łańcuch znakowy z 20000 instrukcji PRINT , a mimo to działa

poprawnie.

Oczywiście zaprezentowany przykład nie ma zastosowania

biznesowego – jest jedynie przykładem obrazującym, że w SQL

Server 2005 nie istnieją znane ograniczenia z wersji SQL Se-

rver 2000.

EXEC('SELECT * FROM dbo.Pracownicy WHERE pensja >= ? ;', 2000)

AT [COMP\SQL_SERVER];

Zwróć uwagę, że w warunku użyto znaku ? . Natomiast wartość

2000 będzie podstawiona właśnie w miejsce tego znaku. Oczy-

wiście zamiast wartości 2000 można wstawić zmienną, tak jak

zaprezentowano to poniżej.

DECLARE

@p_pensja MONEY;

EXEC('SELECT * FROM dbo.Pracownicy WHERE pensja >= ?;', @p_pensja)

AT [COMP\SQL_SERVER];

Oczywiście łańcuch wejściowy w całości może być zmienną.

DECLARE

@p_pensja MONEY,

@sql VARCHAR(100);

SET @sql = 'SELECT * FROM dbo.Pracownicy WHERE pensja >= ' +

CAST(@p_pensja AS VARCHAR);

EXEC(@sql) AT [COMP\SQL_SERVER];

Polecenie EXEC AT

W SQL Server 2005 udostępniono nową składnię EXEC AT

umożliwiającą wykonywanie dynamicznego kodu na połą-

czonym serwerze. Często się zdarza, że aplikacje bizneso-

we pracują na połączonych serwerach, stąd też składnia

EXEC AT może odgrywać ważną rolę.

W SQL Server 2000 istniała funkcja tablicowa OpenQuery ,

która posiadała wiele ograniczeń (m.in. wejściowy łańcuch

znakowy musiał być statyczny).

W SQL Server 2005 rozwiązano te kwestie wprowadza-

jąc składnię EXEC AT .

Aby zademonstrować działanie polecenia trzeba z pozio-

mu instancji SQL Server 2005 utworzyć serwer połączony z

inną instancją SQL Server 2005, do której możliwy jest do-

stęp poprzez użycie polecenia:

Ponadto w łańcuchu mogą znaleźć się instrukcje inne niż SELECT

– można tu umieścić całe bloki kodu. Używając klauzuli EXEC AT

należy pamiętać o kilku aspektach:

• przekazywany kod musi być napisany w dialekcie SQL ser-

wera docelowego, a rodzaj serwera docelowego nie ograni-

cza się tylko do SQL Server. Może nim być dowolny dostaw-

ca, z którym można nawiązać połączenie poprzez ODBC lub

OLE.DB i być obsługiwany jako serwer połączony;

• tworząc serwer połączony do innego dostawcy (np. do

MS Access) trzeba wykonać procedurę sp _ addlinked-

server , a także procedury sp _ droplinkedsrvlogin (w ce-

lu usunięcia wszystkich domyślnych mapowań zwrotnych

dodanych dla wszystkich kont lokalnych, a następnie w

celu zmapowania lokalnego konta z kontem zabezpieczeń

na serwerze docelowym), sp _ serveroption (w celu ze-

zwolenia na używanie wobec połączonego serwera me-

chanizmu zdalnego wywoływania procedur – Remote Pro-

cedura Call – RPC);

• włączając mechanizm RPC zwiększamy na docelowym ser-

werze obszar mogący być celem ataku, dlatego warto zapo-

znać się z informacjami zawartymi w Books OnLine w sekcji

Security for Remote Serwer .

Rysunek 1. Plany wykonania dla dynamicznego zapytania

Instrukcja sp_executesql

Polecenie sp _ executesql jest bardziej funkcjonalne niż EXEC po-

nieważ obsługuje zarówno parametry wejściowe, jak i wyjściowe.

Parametry polecenia sp _ executesql przypominają składniki pro-

cedury składowanej. Różnica jest taka, że w przypadku polecenia

sp _ executesql kod polecenia konstruowany jest dynamicznie.

Software Developer’s Journal 4/2008

www.sdjournal.org

Aplikacje

biznesowe

Zaprezentuję teraz zastosowanie polecenia sp _ execute-

sql w praktyce (Listing 6). Zbuduję i wykonam polecenie SQL

pobierające wszystkich pracowników zarabiających więcej od

wskazanej kwoty. Zauważ, że w przykładzie z Listingu 6 zamiast

dokonywać konkatenacji wartości zmiennej z łańcuchem, używa-

ny jest parametr, który zdefiniowany został w sekcji @params po-

lecenia sp _ executesql . Polecenie sp _ executesql posiada jesz-

cze jedną zaletę. Otóż w przypadku kilkukrotnego uruchomienia

dynamicznego kodu np z różnymi wartościami zmiennej @p _ pen-

sja , generowany jest jeden plan wykonania, a nie jak w przypad-

ku polecenia EXEC , kilka planów. Możemy to zaobserwować usu-

wając plany wykonania.

Listing 2. Przykład użycia dynamicznego kodu

USE Business;

-- deklaracja zmiennych

DECLARE

@schemat AS NVARCHAR ( 10 ) ,

@tabela AS NVARCHAR ( 50 ) ,

@obiekt AS NVARCHAR ( 50 ) ;

-- przypisanie wartosci do zmiennych

DBCC FREEPROCCACHE

SET @schemat = N 'dbo' ;

SET @tabela = N 'Pracownicy' ;

SET @obiekt = QUOTENAME ( @schemat ) + N '.' +

QUOTENAME ( @tabela ) ;

Jeśli wykonamy kilkukrotnie dynamiczny kod, a następnie za-

damy zapytanie:

SELECT objtype, usecounts, sql, cacheobjtype

FROM sys.syscacheobjects

WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sp_executesql%'

AND sql NOT LIKE '%sys.%'

-- wykonanie zapytania SELECT

EXEC ( N 'SELECT COUNT(*) FROM ' + @obiekt + N ';' ) ;

Przekonamy się wtedy, że w przypadku sp _ executesql genero-

wany jest jeden plan wykonania.

Polecenie sp _ executesql może również zwracać wartości

parametrów. Wystarczy zadeklarować parametry jako OUTPUT .

W artykule tym już wspomnieliśmy o ograniczeniach in-

strukcji EXEC . Bardzo podobnie wygląda kwestia limitu instruk-

cji dla polecenia sp _ executesql . W SQL Server 2000 długość

wejściowego łańcucha znakowego była ograniczona do 4000

znaków, w wersji SQL Server 2005 ograniczenie to praktycz-

nie nie istnieje.

Dynamiczne filtry

Jednym z ważniejszych zastosowań dynamicznego kodu SQL jest

obsługa aplikacji biznesowych umożliwiających użytkownikowi wy-

bieranie danych według dynamicznych kryteriów lub dynamiczne

sortowanie danych (według dowolnych kolumn). Oczywiście istnie-

ją alternatywy dla dynamicznego sortowania i filtrowania danych w

postaci rozwiązań statycznych – jednak rozwiązania statyczne pro-

wadzą do bardzo nieefektywnych planów wykonania, co w przy-

padku aplikacji biznesowych jest bardzo często niedopuszczalne.

Zaprezentuję teraz przykład dynamicznego filtrowania danych. Na-

piszę procedurę składowaną (Listing 8), która wyświetli pracowni-

ków i ich zarobki, w zależności od działu lub/i wielkości pensji. Jak

widać na Listingu 8, wyrażenie odpowiedzialne za filtrowanie da-

nych budowane jest za pomocą instrukcji CASE . Stworzoną proce-

durę składowaną można wywoływać na kilka sposobów:

Sp_executesql

i ustawienia środowiskowe

Ustawienia środowiskowe, takie jak kontekst bazy danych i

opcje SET , które zostały wykonane przez zadanie wywołujące

pozostają w mocy także w zadaniu dynamicznym wykonywanym

przez sp _ executesql , ale nie odwrotnie.

Zmiana kontekstu w zadaniu dynamicznym nie wpływa na

kontekst bazy danych dla zadania wywołującego, dlatego w na-

szym przypadku wyświetlony zostanie tekst business . Jeśli wy-

pisanie kontekstu nastąpi w zadaniu dynamicznym, to otrzyma-

my rezultat testowa (Listing 7).

W podobny sposób tego typu zmiany pozostają w mo-

cy dla kolejnych poziomów wewnętrznych w stosunku do za-

dania dynamicznego np. dla zagnieżdżonego poziomu dyna-

micznego.

• pobranie wszystkich pracowników z działu Informatyka za-

rabiających co najmniej 2500 zł

EXECUTE [dbo].[dynamiczny _ iltr] 2500, 'Informatyka';

• pobranie wszystkich pracowników z działu Informatyka

EXECUTE [dbo].[dynamiczny _ iltr] NULL, 'Informatyka';

• pobranie wszystkich pracowników zarabiających co najmniej

2500 zł

EXECUTE [dbo].[dynamiczny _ iltr] 2500, NULL;

• pobranie wszystkich pracowników

EXECUTE [dbo].[dynamiczny _ iltr] NULL, NULL;

Listing 1. Składnia poleceń EXEC i sp_executesql

Wstrzykiwanie SQL

Tworząc dynamiczny kod, bardzo łatwo jest popełnić błąd, któ-

ry sprawi, że potencjalny napastnik będzie mógł w łatwy spo-

sób przejąć kontrolę nad aplikacją. W przypadku aplikacji bizne-

sowych taka sytuacja nie powinna mieć miejsca ze względu na

wartość przechowywanych danych. Doskonałą ilustracją oma-

wianych aspektów jest źle oprogramowany system logowania do

naszej aplikacji. Przyjrzyjmy się fragmentowi kodu z Listingu 9.

Oczywiście przedstawiony na Listingu 9 fragment kodu nie

jest odporny na tzw. wstrzykiwanie SQL, czyli jest niebezpiecz-

EXEC ( ‘łańcuch znakowy’ ) ;

EXECUTE sp_executesql

@stmt = ’instrukcja’, -- różne instrukcje SQL

@params = ‘parametry’, -- tak jak w deklaracji parametrów

funkcji/procedury

<przypisanie parametrów>; -- tak jak w wywołaniu

parametrów funkcji/procedury

www.sdjournal.org

Software Developer’s Journal 4/2008

SQL Server 2005

ny. Wcale nie trzeba znać hasła, żeby zalogować się do bazy

danych. Wystarczy wstawić odpowiednią wartość do zmiennej

@user (Listing 10).

Przedstawiona na Listingu 10 technika włamania polega na

dodaniu znaku cudzysłowu zamykającego oraz wyrażenia OR

1=1 , które staje się częścią filtrującą dane, a następnie doda-

niu znaku komentarza (dzięki temu poprawność hasła nie jest

sprawdzana). Cała instrukcja logująca do aplikacji wygląda w

naszym przypadku następująco:

Listing 4. Kod ukazujący wpływ konkatenacji na plany

zapytań

DECLARE

@p_pensja MONEY,

@sql VARCHAR ( 100 ) ;

SET @p_pensja = 3000;

SET @sql = 'SELECT COUNT(*) FROM dbo.Pracownicy WHERE

pensja >= ' +

CAST ( @p_pensja AS VARCHAR ) + N ';' ;

EXEC ( @sql ) ;

SELECT @p_ilosc = COUNT(*) FROM dbo.Uzytkownicy WHERE Uzytkownik

= ''

OR 1 = 1 --' AND Haslo = ''

Jak bronić się przed przedstawionym wstrzyknięciem kodu? Naj-

lepiej zrezygnować z dynamicznego kodu. Jeśli jest to niemożliwe,

nieco większy poziom zabezpieczenia daje skonstruowanie innego

zapytania (pobierającego np. identyfikator użytkownika, a nie ilość

użytkowników). Jeśli jednak koniecznie chcesz sprawdzać ilość

użytkowników, to zdecydowanie lepszym pomysłem jest użycie pa-

rametrów dotyczących loginu i hasła wewnątrz instrukcji sp _ exe-

cutesql , tak jak to zostało przedstawione na Listingu 11. Przedsta-

wiony na Listingu 11 kod odporny jest na podanie w nazwie użyt-

kownika ciągu ' OR 1 = 1 -- . Jednak w dalszym ciągu jest to kod

niezbyt bezpieczny. W dalszej części artykułu przedstawię kolej-

ne wskazówki pomagające zabezpieczyć dynamiczny kod przed

wstrzykiwaniem. Jednak należy sobie zdawać sprawę, że całkowi-

te zabezpieczenie jest niezwykle trudne i moim zdaniem raczej nie-

możliwe. Zaprezentuję kolejny przykład dynamicznego kodu źró-

dłowego, który z pozoru wydaje się być bezpieczny. Procedura z

Listingu 12 wyświetla w postaci tabeli nazwiska i pensje pracowni-

ków z działu wskazanego przez użytkownika. Z pozoru kod zapre-

zentowany na Listingu 12 wydaje się bezpieczny. Procedurę może-

my wywołać podając nazwę działu jako parametr.

Listing 3 . Trik prezentujący sposób przypisania wyniku

działania kodu źródłowego do zmiennej

EXEC dbo.Pokaz_uzytkownikow Informatyka

DECLARE

@p_pensja MONEY,

@p_liczba INT,

@sql VARCHAR ( 100 ) ;

-- ustawienie wielkości pensji

SET @p_pensja = 3000;

-- dynamiczne stworzenie zapytania pobierającego dane na

temat pracowników

-- zarabiających co najmniej 3000 zł

SET @sql = 'SELECT COUNT(*) FROM dbo.Pracownicy WHERE pensja

>= ' +

CAST ( @p_pensja AS VARCHAR ) + N ';' ;

-- sprawdzenie, czy w bazie istnieje tabela ___tymczasowa

IF OBJECT_ID ( 'dbo.tymczasowa' ) IS NOT NULL

DROP TABLE dbo.tymczasowa; -- jeśli tak, to ją usuwamy

-- tworzymy tabelę od nowa

CREATE TABLE dbo.tymczasowa

(

ilosc INT

) ;

-- wstawiamy dane do tabeli wywołując EXEC w poleceniu

INSERT

INSERT INTO dbo.tymczasowa

EXEC ( @sql ) ;

-- pobieramy dane do zmiennej @p_liczba używając zapytania

SELECT

SELECT @p_liczba = ilosc FROM dbo.tymczasowa;

-- wyświetlamy wartość zmiennej na ekranie

PRINT @p_liczba;

-- usuwamy tabelę tymczasową

DROP TABLE dbo.tymczasowa;

Natomiast osoba, która zna się na wstrzykiwaniu SQL może wy-

wołać tę procedurę z nieco inną wartością parametru:

EXEC dbo.Pokaz_uzytkownikow ''' UNION ALL SELECT NAME, ID FROM

sysobjects--'

Parametr ten składa się z apostrofa zamykającego, polecenia

pobierającego nazwę i identyfikator obiektów bazy danych oraz

znaku komentarza. Dzięki temu zabiegowi polecenie SQL pobie-

rające dane wygląda następująco:

SELECT p.Nazwisko, p.Pensja FROM dbo.Pracownicy p, Dzialy d

WHERE p.Id_dzialu = d.Id_dzialu AND

d.Nazwa = '' UNION ALL SELECT NAME, ID

FROM sysobjects--'

Listing 5. Długi kod źródłowy wykonywany dynamicznie

DECLARE

@licznik INT,

@sql VARCHAR ( MAX ) ,

@tekst VARCHAR ( 10 ) ;

SET @licznik = 1;

SET @sql = '' ;

SET @tekst = 'napis' ;

WHILE @licznik <= 20000

BEGIN

SET @sql = @sql + 'PRINT ''' + @tekst + + '''' + CHAR ( 13 ) +

CHAR ( 10 ) ;

SET @licznik = @licznik + 1;

END

EXEC ( @sql ) ;

Software Developer’s Journal 4/2008

www.sdjournal.org

Aplikacje

biznesowe

i pozwala uzyskać informacje o obiektach systemowych stworzo-

nych przez użytkownika. W kolumnie Nazwisko znajdują się dane

dotyczące nazwy obiektu, a w kolumnie Pensja identyfikator tego

obiektu zamieniony na liczbę zmiennoprzecinkową. Po uzyskaniu in-

formacji o obiektach haker może usunąć jeden lub kilka obiektów:

• SELECT * FROM Oddzialy;

zwróci błąd

Msg 208, Level 16, State 1, Line 1

Invalid object name 'Oddzialy' .

• EXEC dbo.Pokaz _ uzytkownikow ''';DROP TABLE Oddzialy--';

Próba pobrania danych z tabeli Oddzialy po wykonaniu pro-

cedury

Tak więc obiekt został bezpowrotnie usunięty. Nie muszę

chyba tłumaczyć, jakie to może mieć znaczenie w przy-

padku aplikacji biznesowych przechowujących szczegól-

nie cenne dane np. numery kont klientów, kwoty dotyczą-

ce kosztów lub przychodów firmy. Haker może oczywi-

ście sprawdzić, z jakich kolumn składa się wybrana ta-

Listing 6 . Przykład użycia sp_executesql

USE business;

Listing 8. Przykład użycia dynamicznych filtrów

-- deklaracja dwóch zmiennych przechowujących wielkość

-- pensji i treść zapytania SQL

USE business;

-- jeśli w bazie danych istnieje procedura składowana ...

IF OBJECT_ID ( 'dbo.dynamiczny_iltr' ) IS NOT NULL

DROP PROCEDURE dbo.dynamiczny_iltr; -- ... to ją usuwamy

-- tworzymy dynamiczną procedurę z dwoma parametrami

@p_pensja i @p_dzial

CREATE PROCEDURE dbo.dynamiczny_iltr

@p_pensja AS MONEY = NULL ,

@p_dzial AS NVARCHAR ( 20 ) = NULL

BEGIN

DECLARE @sql NVARCHAR ( 1000 ) ; -- deklaracja zmiennej

przechowującej dynamiczne zapytanie

-- budowa dynamicznego zapytania pobierające dane w

zależności ...

SET @sql = N 'SELECT p.Imie, p.Nazwisko, p.Pensja, d.Nazwa

' +

N 'FROM Pracownicy p, Dzialy d ' +

N 'WHERE p.id_dzialu = d.id_dzialu ' +

-- ... od tego, czy podano wielkość pensji ...

CASE

WHEN @p_pensja IS NOT NULL THEN -- jeśli podano wielkość

pensji

N 'AND p.Pensja >= @pensja ' –- to dodawany jest

odpowiedni warunek

ELSE '' –- w przypadku, gdy nie podano pensji dodawany

jest pusty ciąg

DECLARE

@p_pensja INT,

@sql NVARCHAR ( 100 ) ;

-- przypisanie kwoty 2500 zł do zmiennej @p_pensja

SET @p_pensja = 2500;

-- zbudowanie dynamicznego zapytania SQL zawierającego

-- parametr @pensja

SET @sql = 'SELECT * FROM dbo.Pracownicy WHERE Pensja >=

@pensja;' ;

-- wykonanie zapytania

EXEC sp_executesql

-- przypisanie łańcucha zawierającego zapytanie SQL

@stmt = @sql,

-- zdeiniowanie parametru wejściowego

@params = N '@pensja AS MONEY' ,

-- przypisanie do parametru wejściowego wartości zmiennej

END +

-- ... oraz, czy podano dział

CASE

WHEN @p_dzial IS NOT NULL THEN -- jeśli podano dział

N 'AND UPPER(d.Nazwa) = UPPER(@dzial) ' –- to dodawany

jest odpowiedni warunek

ELSE '' –- w przypadku, gdy nie podano działu dodawany

jest pusty ciąg

@pensja = @p_pensja;

-- @p_pensja

Listing 7. Dynamiczny kod i ustawienia środowiskowe

END ;

USE business;

DECLARE

@sql NVARCHAR ( 100 ) ;

-- budowa zapytania sql

SET @sql = 'USE Testowa; SELECT DB_NAME();' ; -- wypisze

Testowa

-- wykonanie zapytania SQL

EXEC sp_executesql

@sql,

N '@pensja AS MONEY, @dzial AS NVARCHAR(20)' ,

@pensja = @p_pensja,

@dzial = @p_dzial;

END ;

-- wykonanie zapytania

EXEC sp_executesql @sql;

www.sdjournal.org

Software Developer’s Journal 4/2008

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: