Trabalhando com express�es CASE e a Fun��o IIF no T-SQL

H� momentos, no decorrer de um projeto, em que precisamos escrever uma �nica instruç�o T-SQL que seja capaz de retornar diferentes express�es com base em avaliaç�es de outras express�es. � em casos como esse que podemos utilizar express�es do tipo CASE ou mesmo funç�es IIF para que nossas necessidades sejam atendidas. E ser� este o tema do nosso artigo. Iremos ent�o ver a sintaxe CASE e IIF e apresentaremos alguns exemplos de como a express�o CASE e funç�o IIF elas funcionam.

A DevMedia preparou pocket videos sobre dicas de Transact-SQL, n�o deixem de conferir.

Express�o CASE

A express�o Transact-SQL CASE nos permite colocar a l�gica condicional em nossos c�digos T-SQL. Esta l�gica condicional nos fornece uma maneira diferente de adiç�o de blocos de c�digo que podem ser executadas em nossas declaraç�es T-SQL, dependendo de uma avaliaç�o booleana da l�gica condicional, nos dizendo se TRUE ou FALSE. Podemos, inclusive, colocar v�rias express�es condicionais em uma �nica express�o CASE. Para melhor compreendermos a forma como a express�o CASE realmente funciona, vamos rever a sintaxe da express�o CASE, apresentada pela Listagem 1, e em seguida, passaremos por uma s�rie de exemplos diferentes que ir�o mostrar alguns conceitos e suas utilidades.

Para começarmos, precisamos observar que a express�o CASE possui dois formatos diferentes, um simples e o reviewed (revisto). Cada um destes tipos possui um formato ligeiramente diferente, ambos est�o sendo apresentados na Listagem 1.

Listagem 1. Sintaxes da express�o CASE.

- Express�o Case simples:
      CASE input_expression 
           WHEN when_expression THEN result_expression [ ...n ] 
           [ ELSE else_result_expression ] 
      END 
      - Express�o Case Searched (busca):
      CASE
           WHEN Boolean_expression THEN result_expression [ ...n ] 
           [ ELSE else_result_expression ] 
      END

Ao analisarmos os dois formatos apresentados para a express�o CASE, presentes na Listagem 1, podemos observar que cada um dos formatos oferece uma forma diferente de identificar uma das m�ltiplas express�es que determinam os resultados da express�o CASE. Em ambos os tipos de CASE, um teste booleano � realizado para cada uma das cl�usulas WHEN. Ap�s a palavra CASE, temos ent�o o nosso termo de pesquisa, o qual � chamado de input_expression, que ser� verificado ap�s o WHEN onde colocamos ent�o a condiç�o que dever� ser satisfeita. Numa express�o CASE simples do operador entre o "input_expression" e o "when_expression" � sempre o operador de igualdade. Enquanto que com a express�o CASE searched, cada cl�usula WHEN ir� conter um "Boolean_expression". Este "Boolean_expression" pode ser uma express�o booleana simples com um �nico operador, ou uma express�o booleana complexa, que pode apresentar v�rias condiç�es diferentes. Al�m disso, a express�o CASE searched pode usar o conjunto completo de operadores booleanos.

Independentemente de qual formato CASE seja utilizado, cada cl�usula WHEN � comparada na ordem em que aparece. O resultado da express�o CASE ser� baseado na primeira cl�usula WHEN que � avaliada como TRUE. Se nenhuma cl�usula WHEN for avaliada como TRUE, ent�o a express�o ELSE � retornada. Quando a cl�usula ELSE � omitida e nenhuma cl�usula WHEN for avaliada como TRUE, em seguida, um valor NULL ser� retornado.

Exemplos de utilizaç�o de express�es CASE

Vamos ent�o começar com alguns testes para esclarecer d�vidas com relaç�o a utilizaç�o das express�es CASE. Para isso, criemos uma nova base de dados chamada de Testes e em seguida, vamos criar a nossa tabela de demonstraç�es, que ser� semelhante a apresentada pela Listagem 2, a qual ser� chamada de OrdemServico. Caso tenham interesse em acompanhar os testes de forma pr�tica, criem ent�o esta tabela em sua inst�ncia do SQL Server, no nosso caso, estamos utilizando o SQL Server 2014.

Listagem 2. Criando a Tabela OrdemServico.

CREATE TABLE OrdemServico (
      ID int identity, 
      OrdemData date, 
      OrdemValor decimal(10,2), 
      Pago char(1));
      INSERT into OrdemServico VALUES 
      ('12-03-2014', 10.49, NULL), 
      ('10-05-2014', 220.45,'S'), 
      ('06-14-2012', 8.62, NULL), 
      ('04-02-2012', 75.39, NULL), 
      ('07-11-2013', 125.54, NULL), 
      ('04-25-2008', 99.99, NULL), 
      ('11-09-2013', 320.13, 'N'), 
      ('04-05-2010', 175.76, NULL), 
      ('03-27-2011', 1.69, NULL);

Como estamos inserindo direto na base de dados os valores, devemos passar as datas e os valores monet�rios no formato americano, mas caso queiram fazer a mudança para formatos diferentes, fiquem a vontade. Sigamos agora para o nosso primeiro exemplo de utilizaç�o.

Usando uma express�o CASE simples com cl�usulas WHEN e ELSE

Para demonstrarmos como funciona a express�o CASE simples, vamos executar primeiro o c�digo presente na Listagem 3.

Listagem 3. Express�o CASE com ELSE.

use Testes;
      SELECT YEAR(OrdemData) AS OrdemAno, 
             CASE YEAR(OrdemData)
                                       WHEN 2014 THEN 'Ano 1'
                                       WHEN 2013 THEN 'Ano 2'
                                       WHEN 2012 THEN 'Ano 3'
                                       ELSE 'Ano 4 e posteriores' END AS TipoAno
      FROM OrdemServico;

Se analisarmos o c�digo apresentado pela Listagem 3 podemos observar que, logo ap�s a palavra CASE, especificamos o ano como sendo "ANO (OrdemData)" e depois seguimos por mais outras cl�usulas, onde cada uma possu�a um ano diferente, começando com o ano de 2014.

Quando a nossa express�o CASE simples � avaliada, ela usa o operador de igualdade ("=") entre o "ANO (OrdemData)" e o Valor, e quando as express�es s�o diferentes. Portanto, o c�digo da Listagem 3 ir� exibir "Ano 1" para a coluna TipoAno que tenha linhas com um valor de ano OrdemData de "2014", ou ent�o ir� exibir "Ano 2" para linhas com um ano OrdemData de "2013" e assim por diante. Se o ano da OrdemData n�o corresponder a nenhuma das cl�usulas passadas, a condiç�o vai exibir "Ano 4 e posteriores". Quando executamos o c�digo presente na Listagem 3, recebemos o resultado presente na Figura 1.

Figura 1. Resultado obtido pelo CASE simples.

Usando uma express�o simples CASE sem o ELSE

Neste exemplo, executaremos o c�digo presente na Listagem 4, que ir� nos mostrar o que acontece quando uma simples express�o CASE n�o possui uma cl�usula ELSE.

Listagem 4. Express�o CASE sem uma cl�usula ELSE.

use Testes;
      SELECT YEAR(OrdemData) AS OrdemAno, 
             CASE YEAR(OrdemData)
                                       WHEN 2014 THEN 'Ano 1'
                                       WHEN 2013 THEN 'Ano 2'
                                       WHEN 2012 THEN 'Ano 3' END AS TipoAno
      FROM OrdemServico;

O c�digo presente na Listagem 4 � como o c�digo apresentado na Listagem 3, mas com a diferença de n�o ter uma cl�usula ELSE especificada. Neste caso, quando executamos o c�digo da Listagem 4, este nos apresenta os resultados, como os mostrados pela Figura 2.

Figura 2. CASE sem a cl�usula ELSE.

Ao analisarmos a sa�da apresentada pela Figura 2, podemos ver que quando o ano do OrdemData na tabela OrdemServico n�o cumpre com qualquer uma das condiç�es para a cl�usula WHEN, o SQL Server exibe "NULL" como valor para o TipoAno.

Usando uma express�o CASE Searched

A express�o simples do CASE com as cl�usulas WHEN foi avaliada com base no operador de igualdade. J� no caso das express�es CASE do tipo searched, podemos fazer uso de outros operadores e a sintaxe da express�o CASE ser� um pouco diferente. Para que possamos demonstrar isso, vamos dar uma olhada no c�digo apresentado pela Listagem 5.

Listagem 5. Express�o CASE do tipo searched.

use Testes;
      SELECT YEAR(OrdemData) AS OrdemAno, 
             CASE 
                                       WHEN YEAR(OrdemData) = 2014 THEN 'Ano 1'
                                       WHEN YEAR(OrdemData) = 2013 THEN 'Ano 2'
                                       WHEN YEAR(OrdemData) = 2012 THEN 'Ano 3'
                                       WHEN YEAR(OrdemData) < 2012 THEN 'Ano 4 e posteriores' END AS TipoAno
      FROM OrdemServico;

Se olharmos para o c�digo apresentado na Listagem 5 podemos perceber que a cl�usula WHEN vem diretamente ap�s a express�o CASE sem nenhum texto entre as duas cl�usulas. Isto diz ao SQL Server que esta express�o � um CASE searched (pesquisado). Al�m disso, observem a express�o booleana que est� ap�s cada cl�usula WHEN. Como voc�s podem ver, nem todas as express�es booleanas est�o usando o operador de igualdade, a �ltima express�o WHEN, por exemplo, usa o operador de menor que ("<"). A express�o CASE que apresentamos na Listagem 5 � logicamente o mesmo que a express�o CASE apresentada na Listagem 3. Por isso, quando executamos o c�digo da Listagem 5, este produz os mesmos resultados, como mostrado pela Figura 1, apresentada anteriormente.

M�ltiplas cl�usulas, mesmo resultado

Podem haver situaç�es em que diferentes cl�usulas WHEN s�o avaliadas como verdadeiro dentro de uma �nica express�o CASE. Ent�o, quando isso acontece, o SQL Server retorna o resultado da express�o associada ao primeiro WHEN que for avaliado como true. No entanto, a ordem de execuç�o das cl�usulas WHEN ir� controlar o resultado obtido que retorna da express�o CASE quando m�ltiplas cl�usulas WHEN s�o avaliadas como TRUE.

Para demonstrarmos como este processo ocorre, iremos usar a express�o CASE para exibir ordens de "500 Reais" quando o OrdemValor estiver dentro do limite dos R$ 500, "300 Reais" quando o OrdemValor estiver dentro da faixa da R$ 300 e "< 200 Reais" quando o OrdemValor for menor que R$ 200 e quando uma OrdemValor n�o estiver de acordo com nenhuma dessas categorias, ent�o classificaremos a ordem como "Valores diferentes do especificado". Vamos ver ent�o o c�digo presente na Listagem 6 para demonstrarmos o que acontece quando m�ltiplas express�es WHEN s�o avaliadas como TRUE ao tentarmos categorizar as ordens em uma dessas categorias de valores Categoria_OrdemValor.

Listagem 6. Exemplo onde m�ltiplas express�es WHEN s�o avaliadas como TRUE.

use Testes;
      SELECT OrdemValor, 
             CASE 
                                       WHEN OrdemValor < 500 THEN 'Abaixo de 500'
                                       WHEN OrdemValor < 300 THEN 'Abaixo de 300'
                                       WHEN OrdemValor < 200 THEN 'Valores menores que 200'
                                       ELSE ' Valores diferentes do especificado.' 
                                           END AS Categoria_OrdemValor
      FROM OrdemServico;

Quando executamos o c�digo presente na Listagem 6 recebemos a sa�da de acordo com a Figura 3.

Figura 3. Resultado da consulta.

Ao analisarmos os resultados da consulta, podemos ver que cada pedido � relatado como sendo abaixo de 500, e n�s sabemos que isso � incorreto. Isso aconteceu porque s� usamos o operador de menor que ("<") para classificarmos de forma simplista as Ordens que levam a m�ltiplas cl�usulas WHEN como resultado para serem avaliadas como verdadeiras na express�o CASE. Por�m, a ordenaç�o das cl�usulas WHEN n�o permitiu que a express�o correta fosse retornada. Reordenaremos ent�o nossas cl�usulas WHEN para que possamos obter os resultados da forma que desejamos. O c�digo presente na Listagem 7 � o mesmo que o da Listagem 6, por�m, aqui temos as cl�usulas reordenadas para categorizar corretamente nossas ordens.

Listagem 7. Pesquisa reordenada para m�ltiplos resultados.

use Testes;
      SELECT OrdemValor, 
             CASE 
                                       WHEN OrdemValor < 200 THEN 'Abaixo de 200'
                                       WHEN OrdemValor < 300 THEN 'Abaixo de 300'
                                       WHEN OrdemValor < 500 THEN 'Valores menores que 500'
                                       ELSE ' Valores diferentes do especificado.' 
                                           END AS Categoria_OrdemValor
      FROM OrdemServico;

Quando executarmos o c�digo da Listagem 7 receberemos a sa�da apresentada pela Figura 4.

Figura 4. Resultado ordenado da pesquisa.

Ao analisarmos a sa�da presente na Figura 4 podemos ver que, alterando a ordem da cl�usula WHEN, obtemos os resultados de forma correta para cada ordem, como realmente deve ser.

Express�es CASE aninhadas

Ocasionalmente voc�s podem ter a necessidade de executar testes adicionais para categorizar ainda mais dados usando a express�o CASE. Quando isso ocorre, voc�s podem usar uma express�o CASE aninhada. O c�digo presente na Listagem 8 mostra um exemplo de aninhamento para categorizar ainda mais as encomendas na tabela OrdemServico que � para determinar se a ordem foi emitida com o resultado Pago quando uma ordem � acima de R$120,00.

Listagem 8. Declarando um CASE aninhado.

use Testes;
      SELECT OrdemValor, 
             CASE 
                                       WHEN OrdemValor < 200 THEN 'Ordem abaixo de 200 Reais'
                                       WHEN OrdemValor < 300 THEN 'Ordem abaixo de 300 Reais'
                                       WHEN OrdemValor < 500 THEN 
                                                      CASE 
                                                                     WHEN Pago = 'S' 
                                                                         THEN 'Ordem acima de 300 Reais sem estar Pago'
                                                                         ELSE 'Ordem de 300 Reais como Pago' END
                                       ELSE  
                                                      CASE 
                                                                     WHEN Pago = 'N' 
                                                                         THEN 'Ordem de 500 Reais sem estar como Pago'
                                                                         ELSE 'Ordem acima de 500 Reais estando como Pago' END 
                                           END AS Categoria_OrdemValor
      FROM OrdemServico;

O c�digo apresentado na Listagem 8 � semelhante ao que apresentamos na Listagem 7, a �nica diferença � que adicionamos uma express�o CASE a mais para ver se um determinado pedido na tabela OrdemServico foi comprado utilizando a opç�o Pago ou n�o, o que s� � permitido em compras acima de 300 Reais. Tenham em mente que quando temos express�es CASE aninhadas, s� s�o permitidos at� 10 n�veis de aninhamento no SQL Server.

Outros lugares onde a express�o CASE pode ser utilizada

At� agora, todos os nossos exemplos usaram express�es CASE para criar uma string de resultados, colocando a express�o CASE na lista de seleç�o de uma instruç�o SELECT do T-SQL. Tamb�m podemos usar uma express�o CASE em instruç�es de UPDATE, DELETE e instruç�es do tipo SET. Al�m disso, a express�o CASE pode ser usada em conjunto com IN, WHERE, ORDER BY e HAVING. Na Listagem 9 estaremos usando uma express�o CASE com uma cl�usula WHERE.

Listagem 9. CASE com a clausula WHERE.

use Testes;
      SELECT *
      FROM OrdemServico
      WHERE CASE YEAR(OrdemData)
                                     WHEN 2014 THEN 'Ano 1'
                                     WHEN 2013 THEN 'Ano 2'
                                     WHEN 2012 THEN 'Ano 3'
                                     ELSE 'Ano 4 e posteriores' END = 'Ano 1';

Na Listagem 9 queremos apenas retornar uma ordem da tabela de OrdemServico para linhas apresentando "Ano 1". Para conseguirmos isso, colocamos a mesma express�o CASE como foi utilizada na Listagem 3 na cl�usula WHERE. Usamos ent�o a express�o CASE como a parte do lado esquerdo da condiç�o WHERE para que ele produza uma sequ�ncia diferente de "Ano ..." com base na coluna OrdemData. Testamos ent�o a string que foi produzida a partir da express�o CASE para ver se era igual ao valor "Ano 1", em caso positivo, uma linha seria devolvida a partir da tabela OrdemServico. N�o recomendamos usar uma express�o CASE para selecionar a data a partir de uma coluna de data dessa forma, pois existem melhores m�todos para isso, como por exemplo, usar a funç�o YEAR para selecionarmos as linhas de um determinado ano. Fizemos dessa forma apenas para demonstrar como usar uma instruç�o CASE com cl�usulas WHERE.

Express�es CASE com a funç�o IIF

A partir da vers�o 2012 do SQL Server, a Microsoft adicionou a funç�o IIF. A funç�o IIF pode ser considerado um atalho para a instruç�o CASE. Na Listagem 10 podemos encontrar a sintaxe para a funç�o IIF.

Listagem 10. Sintaxe da express�o IIF.

IIF ( boolean_expression, true_value, false_value )

O "Boolean_expression" � uma express�o booleana v�lida que equivale a VERDADEIRO ou FALSO. Quando a express�o booleana � igual a um valor VERDADEIRO, em seguida, a express�o "true_value" � executada. Se a express�o booleana equivale a FALSE ent�o o "false_value" � executado. Assim como a express�o CASE, a funç�o IIF pode ser aninhada at� 10 n�veis.

Para demonstrarmos como a funç�o IIF pode ser usada para substituir a express�o CASE, vamos rever o c�digo no qual usamos a express�o CASE searched presente na Listagem 11.

Listagem 11. Exemplo simples de utilizaç�o da express�o CASE.

use Testes;
      SELECT OrdemValor,
             CASE 
                                       WHEN OrdemValor > 200 THEN 'Acima do valor'
                                       ELSE 'Abaixo do valor' END AS OrdemType
      FROM OrdemServico;

O c�digo presente na Listagem 11 possui apenas uma �nica express�o WHEN que � utilizada para determinar se o OrdemValor � uma ordem de alta ou baixa do valor especificado. Se a express�o WHEN "OrdemValor > 200" for avaliada como TRUE, ent�o o valor OrdemType est� definido para "Acima do valor". Se a express�o WHEN � avaliada como FALSE ent�o "Abaixo do valor" ser� definido para o valor OrdemType.

O c�digo reescrito da funç�o que usamos em vez de uma express�o CASE IIF pode ser encontrado presente na Listagem 12.

Listagem 12. Exemplo utilizando a express�o IIF.

use Testes;
      SELECT OrdemValor,
                        IIF(OrdemValor > 200,
                                      'Acima do valor',
                                      'Abaixo do valor') AS OrdemTipe
      FROM OrdemServico;

Ao olharmos para a Listagem 12 podemos ver o porqu� da funç�o IIF ser considerada uma vers�o abreviada da express�o CASE. A palavra CASE � substitu�da pelo "IIF ("string, a cl�usula "ent�o" � substitu�da por uma v�rgula, a cl�usula "sen�o" � substitu�do por uma v�rgula e a palavra "END" � substitu�da por um ")". Quando a express�o booleana "OrdemValor > 200". � verdade que o valor " Acima do valor " � exibido. Quando a express�o booleana 'OrdemValor > 200 " � avaliada como FALSE ent�o o" Abaixo do valor" � exibido. Se executarmos o c�digo presente nas Listagem 11 e 12, veremos que ambos produzem o mesmo resultado.

Aninhamento com a funç�o IIF

Assim como a express�o CASE do SQL Server permite que aninhemos funç�es IIF. Na Listagem 13 temos um exemplo de aninhamento com a funç�o IIF.

Listagem 13. Exemplo da funç�o IIF aninhada.

use Testes;
      SELECT OrdemValor, 
             IIF (OrdemValor < 130, 
                              'Abaixo de 130',
                              (IIF (OrdemValor < 200, 
                                              'Abaixo de 200',
                                    (IIF (OrdemValor < 300,
                                           (IIF (Pago = 'N', 
                                                                                 'Ordem de 200 Sem ser como Pago',
                                                                                 'Ordem de 200 como  Pago'
                                                                                 )
                                                                            ),
                                                                            (IIF (Pago = 'N', 
                                                                                  'Ordem de 300 Sem ser como Pago',
                                                                                  'Ordem de 300 como Pago'
                                                                                 )
                                                      )
                                                                           )
                                                                      )
                                               )
                                     )
                                     ) AS Categoria_OrdemValor
      FROM OrdemServico;

Neste exemplo podemos ver que usamos a funç�o IIF v�rias vezes. Cada um adicional � usado tanto no "valor verdadeiro" quanto no "valor falso" da funç�o IIF. O c�digo presente na Listagem 13 � equivalente ao c�digo que usa a express�o CASE aninhada presente na Listagem 8.

Limitaç�es quanto ao uso das express�es CASE e IIF

Assim como ocorre com a maioria das funcionalidades presentes no T-SQL, existem limitaç�es quanto ao uso das mesmas. Abaixo est�o algumas limitaç�es a serem observadas em relaç�o ao uso das funç�es IIF e express�es CASEs.

Limitaç�es para a express�o CASE

Podemos apenas ter at� 10 n�veis de aninhamento em suas express�es.
Express�es CASE n�o podem ser utilizadas para controlar o fluxo de execuç�o de instruç�es T-SQL.

Limitaç�o da funç�o IIF:

Podemos ter at� 10 n�veis de aninhamento de cl�usulas IIF.

Com isso finalizamos mais este artigo sobre T-SQL, onde mostramos que a express�o CASE e a funç�o IIF nos permitem colocar a express�o l�gica no c�digo T-SQL que vai mudar os resultados de nosso c�digo com base nos resultados avaliados de uma express�o. Usando a express�o de comparaç�o suportada pela funç�o IIF e a express�o CASE podem ter diferentes blocos de c�digos executados, dependendo se a express�o de comparaç�o � avaliada como VERDADEIRO ou FALSO. A express�o CASE e a funç�o IIF nos fornecem o controle program�tico para atender aos requisitos do nosso neg�cio de forma mais simples e de f�cil utilizaç�o.

At� a pr�xima!