SQL Procedury s parametrem

Zveřejněno 07 září 06 11:31 dop.

Při psaní uložených procedur, které vrací záznamy dle předaného parametru do procedury se občas stane, že
je třeba vrátit všechny záznamy bez ohledu na hodnotu vstupního parametru procedury. Samozřejmě, že problém by bylo možné řešit
podmínkou IF ELSE, nicméně u rozsáhlých SQL dotazů je pak následná údržba SQL kódu náročnější (je třeba vše upravit 2x).

Příklad:
Mějme tabulku Osoba ve které každá osoba patří do nějakého oddělení (sloupec ID_Oddeleni).
Vytvořime proceduru OsobyDleOddeleni, kde vstupní parametr procedury bude @ID_Oddeleni INT.
Uvnitř procedury napíšeme následující SQL kód

SELECT * FROM osoba WHERE ID_Oddeleni = @ID_Oddeleni

Problém je v tom, že procedura neumí vrátit VŠECHNY osoby v případě, že vstupní parametr bude roven hodnotě NULL.
Proto lze přepsat SQL kód následovně:

SELECT * FROM osoba WHERE ID_Oddeleni = ISNULL(@ID_Oddeleni, ID_Oddeleni)

Tento SQL kód v případě, že vstupní parametr procedury bude mít hodnotu NULL, vrátí všechny osoby.
Další možností je použití méně čtivého zápisu.

SELECT * FROM  Osoba
WHERE (@Id_Oddeleni is null ) OR (@Id_Oddeleni is not null AND Id_Oddeleni = @Id_Oddeleni)

by opis

Komentář

# Mifko said on září 7, 2006 11:56:
Este moze byt zlahcena forma posledneho zapisu a to:

SELECT * FROM Osoba
WHERE
(@Id_Oddeleni is null ) OR (Id_Oddeleni = @Id_Oddeleni)
# filc said on září 7, 2006 12:01:
:) select * ... vas ma asi vase databaze hodne rada, ze vam jeste nepolamala vsechny ruce a muzete psat :D
# rob said on září 7, 2006 12:15:
No ta varianta s
ID_Oddeleni = ISNULL(@ID_Oddeleni, ID_Oddeleni)
asi nebude nejefektivnejsi - na to asi SQL server nebude moci pouzit index...
# opis said on září 7, 2006 12:21:
varianta s ID_Oddeleni = ISNULL je na SQL serveru asi tak 2x pomalejsi (zavisi dle ruznorodosti hodnot ve sloupci)
# opis said on září 7, 2006 12:28:
ad select * ... )
Co Vam na to mam napsat ? radeji nic...
Snad jen, ze o to asi moc nejde...
# Lucius said on září 7, 2006 12:51:
Nema to byt kvuli indexovani takto?

SELECT * FROM Osoba
WHERE (@Id_Oddeleni is null ) OR (Id_Oddeleni is not null AND Id_Oddeleni = @Id_Oddeleni)


# tiny said on září 9, 2006 18:01:
Nedalo mi to a skusil som si vykonnostne porovnat spominane podoby zapisu, podla mna su totiz ekvivalentne a sql server ich musi vediet zoptimalizovat do rovnakej podoby.

Porovnaval som execution plan, ak to niekto nepovazuje za releventny sposob porovnania tak nech sa ozve.

Oba zapisy:
SELECT *
FROM [AdventureWorks].[Person].[Address] a
where a.AddressID = isnull(@addressID, a.AddressID

SELECT *
FROM [AdventureWorks].[Person].[Address] a
where @addressID is null or (@addressID = a.AddressID)

mali uplne rovnaky execution plan, aj client statistics vychadzali v priemere rovnako.

Treti spominany sposob:
SELECT *
FROM [AdventureWorks].[Person].[Address] a
where @addressID is null or (@addressID is not null and @addressID = a.AddressID)

mal execution plan tvoreny rovnakymi castami, len percenta boli trocha inak rozdelene (ta podmienka navyse @addressID is not null zabrala cas navyse). Tento treti sposob aj v client statistics vychadzal najpomalsie (len drobne).

Pre mna z toho vychadza, ze treba pouzivat to co je najprehladnejsie t.j. variantu s ISNULL.
# Lucius said on září 10, 2006 13:19:
DECLARE @sSelect VARCHAR(50)

DECLARE @tblTest TABLE (
[iId] INT identity (1,1),
[iData] INT,
[sData] VARCHAR(50)
)

INSERT INTO @tblTest(iData, sData)VALUES(1, 'AAA');
INSERT INTO @tblTest(sData)VALUES('BBB');
INSERT INTO @tblTest(iData)VALUES(3);

SELECT * FROM @tblTest AS a
SELECT * FROM @tblTest AS a WHERE a.[sData] = ISNULL(@sSelect, a.[sData])

-- Řekl bych, že zmiňovaný příkaz s ISNULL, nefunguje pro NULL hodnoty.
# tiny said on září 10, 2006 17:03:
Ano to ISNULL nefunguje pre NULL hodnoty v tvojom pripade. Vsimni si ale podla akeho stlpca boli tie povodne selecty. Ked vyberas podla primarneho kluca (resp. staci podla hociakeho stlpca, ktory nedovoluje NULL) tak to funguje tak ako autor chcel.
# pavel said on září 12, 2006 6:48:
Toto jsem řešil už několikrát,, stačí to udělat jednoduše, např

WHERE IsNull(a.id,-1) = IsNull(@id,IsNull(a.id,-1))
a máte tam i NULL hodnoty.
# Vande said on listopadu 14, 2007 10:39:

Problém tohoto řešení je skutečně v tom, že se při něm nepoužívají indexy. Možná pro malý objem dat vypadá execution plan stejně, ale pro obří tabulky je toto řešení nepoužitelné.

Kdysi jsem toto "elegantní" řešení použil v jedné bance a pak jsem se divil proč dotaz běžel 10 minut oproti 1 sekundě (byl-li dotaz napsán natvrdo bez IsNull, Coallesce a podobných variant).

Je to smutné, ale asi neexistuje žádné elegantní řešení jak udělat dynamickou WHERE podmínku na MSSQL. Nejjednodušší je asi skládat sql dotaz do řetězce a ten potom spustit.

Neregistrovaní uživatele nemužou přidávat komentáře.
Vyvojar.cz na prodej!