Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

Zanim się zdziwisz – zmienne w T-SQL

Użycie zmiennych w T-SQL może czasami prowadzić do “dziwnych”, niespodziewanych efektów.

Przećwiczmy więc takie przypadki.

DECLARE @tabela TABLE(id int, col varchar(100))

INSERT INTO @tabela
SELECT 1, 'jeden'
UNION ALL
SELECT 3, 'dwa'
UNION ALL
SELECT 3, 'trzy'

DECLARE @res varchar(100)

I. Przypadek “trigger’owy”.

Często spotyka się w trigger’ach taką oto konstrukcję:

SELECT @res = INSERTED.col FROM INSERTED

Konstrukcja jak najbardziej “naganna” ponieważ zakłada, że tabela INSERTED zawiera tylko jeden rekord (może to być szczególny przypadek, ale zawsze należy zakładać że będzie ich więcej). Zobaczmy co się stanie właśnie wtedy gdy tych rekordów będzie więcej, czyli tak jak w przypadku naszej tabeli @tabela dla wartości kolumny id równej 3.

SELECT @res = col
FROM @tabela 
WHERE t.id = 3

SELECT @res

Otrzymujemy:

image

Czyli ostatni rekord z tabeli. Aby zapewnić aby zapytanie zwracało nam dokładnie jeden wynik powinniśmy zastosować:

SELECT TOP 1 @res = t.col
FROM @tabela t
WHERE t.id = 3

SELECT @res

Ale w tym wypadku otrzymujemy:

image

Czyli otrzymaliśmy dwie różne wartości przypisane do zmiennej. W pierwszym przypadku liczyliśmy, że wynikiem zapytania będzie skalar, w drugim przypadku wymusiliśmy aby nim był.

II. Przypadek – jedna zmienna do wielu zapytań.

Uzbrojeni w wiedzę z przypadku zastosujemy ją do przypadku, gdy jedna zmienna będzie wykorzystywana do wielu zapytań. Zastosujemy przypisane do zmiennej wartości z tabeli, co do której będziemy pewni, że wyszukiwanie zwróci nam konkretny rezultat skalarny a w drugim przypadku zastosujemy zapytanie, które zwraca pusty rezultat. Spodziewamy się, że zmienna najpierw będzie miała wartość skalarną, a w drugim przypadku przyjmie wartość NULL. Zastosujmy następujący kod:

SELECT TOP 1 @res = col
FROM @tabela
WHERE id = 3

SELECT @res

SELECT TOP 1 @res = col
FROM @tabela
WHERE id = 4

SELECT @res

Jaki otrzymaliśmy wynik?

image

Wbrew oczekiwaniom, nie nastąpiło przypisanie wartości NULL do zmiennej. Jak temu zapobiec? W sposób następujący:

SET @res = (SELECT TOP 1 col
            FROM @tabela
            WHERE id = 3)

SELECT @res

SET @res = (SELECT TOP 1 col
            FROM @tabela
            WHERE id = 4)

SELECT @res

Tym razem otrzymujemy spodziewany wynik:

image

Widać z tego, że ustawiając wartość zmiennej na podstawie zapytania do bazy danych należy stosować konstrukcję

SET @res = (SELECT TOP 1 …)

zamiast

SELECT @res = TOP 1 …

Użycie TOP 1 w pierwszym wypadku jest niezbędne, ponieważ przy niejednoznacznym wyniku zapytania

SET @res = (SELECT col 
                    FROM @tabela t
                    WHERE t.id = 3 )

Zostaniemy “ukarani” komunikatem błędu:

Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Podsumowując, kluczem do poprawności podstawiania wartości skalarnych do zmiennych jako wyniku zapytania jest zapewnienie, że zapytanie zwraca wartość skalarną nawet bez zastosowania klauzuli TOP 1. Oraz do przypisywania zmiennej wartości z bazy danych stosujemy instrukcję do tego przeznaczoną czyli SET.

Opublikowane 12 lipca 2009 18:42 przez marekpow

Komentarze:

 

dotnetomaniak.pl said:

Dziękujemy za publikację - Trackback z dotnetomaniak.pl

lipca 12, 2009 18:37
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems