COALESCE , para que serve

Question

Para que serve a função

COALESCE

Obrigado

Answer 1

Bom dia,

Segue a documentação da Microsoft a respeito da sua dúvida:

http://msdn.microsoft.com/pt-br/library/ms190349.aspx

---

Att, Bruno Silva.

Answer 2

JCEOMS,

Veja se este exemplo ajuda:

Exemplo 1:

Declare @TBValorString Table
 (Codigo Int Identity(1,1),
  ValorString1 Varchar(10) Default Null,
  ValorString2 Varchar(10) Not Null)

Insert Into @TBValorString (ValorString1, ValorString2)
Values ('AAA','123'), 
       (Null,'456'),
	   (Null,'789'), 
       ('BBB', '901')

-- Utilizando Case --
Select Case
        When Len(ValorString1) Is Null Then 'Null '+ValorString2
       Else
	    ValorString1+ValorString2
	   End As 'Concatenado com Case'
From @TBValorString

-- Utilizando Coalesce --
Select Coalesce(IsNull(ValorString1,'Null'+ValorString2),ValorString1)+
       Coalesce(IsNull(ValorString2, ValorString2+'Null'),ValorString2) As 'Concatenado com Coalesce'
From @TBValorString
Go

Exemplo - 2

IF OBJECT_ID('dbo.wages') IS NOT NULL
    DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
    emp_id        tinyint   identity,
    hourly_wage   decimal   NULL,
    salary        decimal   NULL,
    commission    decimal   NULL,
    num_sales     tinyint   NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
    (10.00, NULL, NULL, NULL),
    (20.00, NULL, NULL, NULL),
    (30.00, NULL, NULL, NULL),
    (40.00, NULL, NULL, NULL),
    (NULL, 10000.00, NULL, NULL),
    (NULL, 20000.00, NULL, NULL),
    (NULL, 30000.00, NULL, NULL),
    (NULL, 40000.00, NULL, NULL),
    (NULL, NULL, 15000, 3),
    (NULL, NULL, 25000, 2),
    (NULL, NULL, 20000, 6),
    (NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary',
       COALESCE(hourly_wage * 40 * 52,salary,commission * num_sales) As 'Total Salary II',
	   (hourly_wage * 40 * 52),
	   salary,
	   (commission * num_sales) As 'Total Salary III'
FROM dbo.wages
ORDER BY 'Total Salary';
GO

---

Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com