Логическая функция CHOOSE в T-SQL – описание и примеры использования

Всем привет! Сегодня мы поговорим о функции CHOOSE языка T-SQL, которая появилась еще в 2012 версии Microsoft SQL Server. Благодаря этой функции мы можем упрощать SQL запросы, в которых используется условная конструкция CASE, и тем самым повышать читабельность SQL инструкций, не снижая их производительности.

Функция CHOOSE языка T-SQL

CHOOSE – логическая функция языка T-SQL, которая возвращает элемент по указанному индексу из списка значений.

Иными словами, функция CHOOSE действует подобно индексу массива, т.е. мы можем из некого массива данных возвращать одно значение этого массива, которое соответствует определенному индексу.

Как было уже отмечено, функцию CHOOSE можно использовать для замены конструкции CASE в SQL запросах, так как принцип ее работы эквивалентен работе конструкции CASE, однако функция CHOOSE может уменьшить количество строк кода и повысить его читабельность, тем самым в целом упрощая программирование с использованием языка T-SQL.

Синтаксис функции CHOOSE

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

где

• index – целочисленное выражение, которое представляет отсчитываемый от 1 индекс в списке элементов, следующих за ним. Если указанное значение индекса имеет числовой тип, отличный от типа INT, то значение неявно преобразуется в целое. Если значение индекса выходит за границы массива значений, то функция CHOOSE возвращает значение NULL;
• value_1, value_2… – список значений любого типа данных, в качестве разделителя выступает запятая.

Функция CHOOSE возвращает тип данных с наивысшим приоритетом из набора типов данных, которые были переданы в функцию.

Заметка! Чем отличается инструкция THROW от RAISERROR в T-SQL.

Примеры использования функции CHOOSE в T-SQL

Давайте рассмотрим несколько примеров использования функции CHOOSE в языке T-SQL.

В каждом примере будет представлено два SQL запроса: первый — с использованием функции CHOOSE, а второй, эквивалентный запрос, но только с использованием конструкции CASE, который будет возвращать точно такой же результат, чтобы более наглядно был виден принцип работы функции CHOOSE.

Простой пример использования функции CHOOSE

Допустим, у нас есть список целочисленных значений и нам нужно для каждого значения выводить соответствующее текстовое описание, и для этого мы можем использовать функцию CHOOSE.

Функция CHOOSE

   
   SELECT CHOOSE (3, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number;

В этом примере мы первым параметром передали индекс 3, а затем через запятую перечислили список значений, в результате функция нам вернула третье по счету значение (текст «Три»). Если бы мы передали 1, то нам вернулось бы первое по счету значение, т.е. текст «Один».

Конструкция CASE

Чтобы получить точно такой же результат, но с использованием конструкции CASE, мы могли бы написать следующую инструкцию, которая абсолютно эквивалентна.

   
   DECLARE @number INT = 3;
   SELECT CASE @number WHEN 1 THEN 'Один'
		       WHEN 2 THEN 'Два'
		       WHEN 3 THEN 'Три'
		       WHEN 4 THEN 'Четыре'
		       WHEN 5 THEN 'Пять'
	  END AS number;

Пример использования функции CHOOSE с участием столбца таблицы

Теперь давайте сделаем практически то же самое, т.е. будем выводить для целочисленного значения соответствующее текстовое описание, но только в этом случае это целочисленное значение будет у нас храниться в столбце таблицы.

Допустим, у нас будет таблица с заказами orders, в которой есть атрибут status, отражающий статус каждого заказа, где:

• 1 = В обработке
• 2 = Выполнен
• 3 = Отменен

Таким образом, нам необходимо в запросе выводить для каждого заказа соответствующий текст статуса.

Исходные данные

Инструкция создания тестовых данных.

   
   CREATE TABLE orders (
	id INT NOT NULL IDENTITY(1,1),
	number INT NOT NULL,
	summa NUMERIC(18,2) NOT NULL,
	status TINYINT NOT NULL
   );

   INSERT INTO orders (number, summa, status)
	VALUES (32152, 1580, 1),
		   (33568, 9600, 2),
		   (41289, 3400, 1),
		   (42870, 6100, 2),
		   (43987, 1900, 3);

   SELECT * FROM orders;

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL.

Функция CHOOSE

Для решения нашей задачи SQL запрос с использованием функции CHOOSE будет выглядеть следующим образом.

   
   SELECT id AS order_number,
	  summa AS order_summa,
	  status AS order_status_id,
	  CHOOSE (status, 'В обработке', 'Выполнен', 'Отменен') AS order_status
   FROM orders;

Конструкция CASE

Точно такой же результат сформирует и следующий запрос, в котором используется конструкция CASE.

   
   SELECT id AS order_number,
	  summa AS order_summa,
	  status AS order_status_id,
	  CASE status WHEN 1 THEN 'В обработке'
		      WHEN 2 THEN 'Выполнен'
		      WHEN 3 THEN 'Отменен' 
	  END AS order_status
   FROM orders;

Заметка! Статистика в Microsoft SQL Server – что это такое и для чего она нужна.

Примеры некорректного использования функции CHOOSE

При использовании функции CHOOSE стоит помнить о нескольких важных моментах, которые уже упоминались, к ним относятся:

• Значение индекса должно быть целочисленным;
• Если значение индекса выходит за границы массива значений, то функция CHOOSE возвращает значение NULL;
• Если значение индекса имеет числовой тип, отличный от типа INT, то значение неявно преобразуется в целое;
• Функция CHOOSE возвращает тип данных с наивысшим приоритетом из набора типов данных, которые были переданы в функцию.

Чтобы лучше понять принцип работы функции CHOOSE, давайте рассмотрим примеры некорректных запросов, и тем самым в действии увидим эти важные моменты, про которые стоит помнить.

Пример ошибки преобразования типа данных в целое число

Если в качестве индекса передать текстовую строку, то запрос завершится ошибкой, так как текст нельзя преобразовать в целое число.

    
   SELECT CHOOSE ('Два', 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number;

Пример передачи значения вне диапазона

Индекс в функции CHOOSE начинается с числа 1, поэтому если мы передадим в качестве индекса значение 0, то функция вернет NULL.

Функция CHOOSE также вернет NULL, если мы передадим значение индекса вне диапазона массива значений, например, у нас перечислено 5 значений в массиве, и если мы передадим значение индекса 6, то нам вернется NULL.

   
   SELECT CHOOSE (0, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_0,
          CHOOSE (6, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_6;

Заметка! Ошибка «Сохранение изменений запрещено» в среде SSMS. Причины и способы устранения.

Пример преобразования типа данных в целое число

Если в качестве значения индекса мы передаем числовое значение, которое не является целочисленным, но может быть преобразовано в целое числе, например, мы передаем число с десятичной запятой, то функция CHOOSE выполнит округление в меньшую сторону.

В данном случае все значения, которые мы передаем в качестве индекса округляются до 1, и тем самым возвращается первое значение массива.

   
   SELECT CHOOSE (1.4, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_1,
	  CHOOSE (1.5, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_2,
	  CHOOSE (1.6, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_3,
	  CHOOSE (1.9, 'Один', 'Два', 'Три', 'Четыре', 'Пять') AS number_4;

Пример ошибки преобразования типа данных в списке значений за счет приоритета типа данных

Если массив значений, который передан в функцию, содержит разные типы данных, то будет возвращен тип данных с наивысшим приоритетом, таким образом, если указать индекс значения, тип данных которого не может быть преобразован в тип данных с наивысшим приоритетом, то функция вернет ошибку.

В данном случае в массиве значений у нас есть значения, имеющие разный тип данных, в частности у нас есть целочисленный тип данных и текстовый, таким образом, в данном случае функция будет возвращать целочисленный тип данных, так как этот тип данных имеет более высокий приоритет, чем текстовый.

Таким образом, если указывать индекс с 1 по 4, то функция отработает корректно, однако если мы укажем индекс 5, т.е. мы хотим получить пятое значение массива, которое имеет текстовый тип данных, то нам вернется ошибка, так как данное текстовое значение не может быть преобразовано к целочисленному типу данных.

   
   SELECT CHOOSE (5, 10, 20, 30, 40, 'Пятьдесят') AS number;

Заметка! Для чего нужны пользовательские функции в T-SQL.

На сегодня это все, надеюсь, материал был вам полезен, пока!