Nowości w SQL Server 2012 – funkcje logiczne i tekstowe

2012-05-03 

W tym wpisie omówię kolejne cztery funkcje, które pojawiły się w najnowszej wersji języka T-SQL. Będą to dwie funkcje logiczne (IIF i CHOOSE) oraz dwie funkcje operujące na ciągach znaków (CONCAT i FORMAT).

IIF

Funkcja w swoim działaniu przypomina wyrażenie CASE. W zależności od wyniku warunku logicznego zwraca jedną z przekazanych wartości, przy czym mogą one być różnych typów. Typ wartości zwracanej przez funkcję określany jest na podstawie hierarchii typów, co oznacza, że będzie on przejęty z parametru o wyższym priorytecie typu. W związku z tym, przekazane wartości parametrów muszą pozwalać na konwersję pomiędzy ich typami. Szczegóły dotyczące hierarchii typów: Data Type Precedence.

IIF(boolean_expression, value_if_true, value_if_false)

boolean_expression – warunek logiczny
value_if_true – wartość zwracana w przypadku spełnienia warunku logicznego
value_if_false – wartość zwracana w przypadku nie spełnienia warunku logicznego

--Prawda
select IIF(1 < 2, 'Prawda', 'Fałsz')

--2
declare @x int = 1, @y int = 2
select IIF(@x > @y, @x, @y)

--Conversion failed when converting the varchar value 'Prawda' to data type int.
select IIF(1 < 2, 'Prawda', 1)

W przypadku bezpośredniego przekazania wartości NULL w miejsce obu zwracanych wartości, wstąpi błąd związany z brakiem możliwości określenia typu wynikowego funkcji:

--At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
select IIF(1 > 2, null, null)

Taki zapis jest już prawidłowy, ponieważ znany jest typ przynajmniej jednego z parametrów:

declare @n int = null
--NULL
select IIF(1 > 2, @n, null)

CHOOSE

Funkcja jako parametr przyjmuje wartość indeksu (typ int) oraz listę elementów w postaci kolejnych parametrów różnych typów. W wyniku zwraca wartość parametru przekazanego na pozycji określonej przez indeks. W przypadku podania indeksu większego niż liczba parametrów zwrócona zostanie wartość NULL. Podobnie jak w funkcji IIF, typ wartości zwracanej przez funkcję CHOOSE określany jest na podstawie elementu o najwyższym priorytecie typu, w związku z czym wartości pozostałych parametrów muszą pozwalać na konwersję do niego.

CHOOSE(index, value_1 [... , value_n])

index – pozycja elementu, którego wartość ma być zwrócona, pozycje liczone od 1 (typ int)
value_1 ... value_n – lista kolejnych parametrów (elementów)

--103
select CHOOSE(3, 101, 102, '103', '104', '105')

--Test1
select CHOOSE(1, 'Test1', 'Test2', 'Test3')

--NULL
select CHOOSE(5, 1, 2, 3)

--Conversion failed when converting the varchar value 'Test1' to data type int.
select CHOOSE(1, 'Test1', 2)

CONCAT

Funkcja pozwala na łączenie kilku ciągów znaków w jeden łańcuch. Jej główną zaletą jest obsługa wartości NULL, które zamieniane są na puste ciągi znaków dzięki czemu nie ma potrzeby używania funkcji ISNULL.

--123456
select CONCAT('123', null, '456')

--''
select CONCAT(null, null)

declare @s1 varchar(3) = '123', @s2 varchar(3) = null, @s3 varchar(3) = '456'
--NULL
select @s1 + @s2 + @s3
--123456
select ISNULL(@s1, '') + ISNULL(@s2, '') + ISNULL(@s3, '')
--123456
select CONCAT(@s1, @s2, @s3)

FORMAT

Funkcja służy do konwersji wartości różnych typów na odpowiednio sformatowany łańcuch znaków. Właśnie możliwość sformatowania ciągu wynikowego jest największą zaletą tej funkcji. W przypadku prostych konwersji, nie wymagających określonego formatu wyjściowego powinno się używać dotychczasowych funkcji CAST lub CONVERT.

FORMAT(value, format [, culture])

value – wartość do skonwertowania
format – format wynikowego łańcucha znaków (typ nvarchar)
culture – opcjonalne wskazanie ustawień regionalnych w jakich sformatowany będzie łańcuch znaków, domyślnie używane są ustawienia bieżącej sesji

Parametr określający format łańcucha znaków musi być poprawnie zdefiniowanym wyrażeniem formatującym znanym z platformy .NET, więcej na ten temat: Formatting Types.

W przypadku błędu podczas formatowania zwracana jest wartość NULL, w przypadku błędnie określonego parametru ustawień regionalnych zgłaszany jest błąd.

declare @d date = '20120402', @m money = 1512845.15

--1 512 845,15 zł
select FORMAT(@m, 'C', 'Pl-PL')

--2 kwietnia 2012
select FORMAT(@d, 'D', 'Pl-PL')

--2012-04
select FORMAT(@d, 'yyyy-MM')

--Pn, kwi 2, 2012
select FORMAT(@d, 'ddd, MMM d, yyyy', 'Pl-PL')

--poniedziałek, 02 kwietnia, 2012
select FORMAT(@d, 'dddd, dd MMMM, yyyy', 'Pl-PL')

Format możemy także przekazać w postaci zmiennej lub kolumny tabeli w zapytaniu:

declare @d date = '20120402', @format varchar(50) = 'd MMMM, yyyy roku (dddd)'

--2 kwietnia, 2012 roku (poniedziałek)
select FORMAT(@d, @format, 'Pl-PL')

Więcej informacji o nowościach w SQL Server 2012: MSDN 

Reklamy

Posted on 2012-05-03, in SQL Server and tagged , , , , . Bookmark the permalink. 3 Komentarze.

  1. krzysztofb

    Rozumiem, że IIF jako funkcja wylicza wartości wszystkich trzech argumentów i dopiero potem coś wybiera..

    • Funkcja IIF w pierwszej kolejności sprawdza warunek logiczny podany w pierwszym parametrze i na podstawie jego wyniku zwraca albo wartość drugiego parametru (w przypadku spełnienia warunku) albo wartość trzeciego parametru (w przypadku nie spełnienia warunku) skonwertowaną na ustalony typ wynikowy. Jeżeli wykonasz polecenie:

      select IIF(1 != 2, 1, Cast(‚Test’ as int))

      w wyniku otrzymasz 1, czyli nie została sprawdzona wartość trzeciego parametru (gdyby była sprawdzana zgłoszony zostałby wyjątek). Jeżeli zmienisz warunek na:

      select IIF(1 = 2, 1, Cast(‚Test’ as int))

      wtedy dopiero pojawi się błąd konwersji przy próbie zwrócenia wartości trzeciego parametru.

  1. Pingback: SQL Server 2012 – Funkcja FORMAT « SQLGeek.pl

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: