Microsoft ornek linki http://msdn.microsoft.com/en-us/library/ms190349.aspx Database`den veri çekerken null degerlerin geri dönmesi her zaman canımı sıkmıstır. Access`de iif, Sql Server`da case ile degerleri replace ettirsekte sorunu çözsekde aslında bu isi yapabilecek daha kolay bir fonksiyon bulunmaktadır. Bu fonksiyonun adı COALESCE` dır. Bu fonksiyon veri çekerken field içersindeki null degeri istedigimiz deger ile replace etmemizi saglar. Kullanımı asagıdaki gibidir; Kulanımı Select top 5 FirstName, LastName, COALESCE(MiddleName,`-`) as MiddleName -- Eger MiddleName`deki deger NULL ise yerine "-" yazacak. From Person.Contact Sonuç Gustavo Achong - Catherine Abel R. Kim Abercrombie - Humberto Acevedo - Pilar Ackerman - (5 row(s) affected) ---------------------------------- --DIGER ORNEKLER http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/ DECLARE @int INT, @datetime DATETIME; SELECT COALESCE(@datetime, 0); SELECT COALESCE(@int, CURRENT_TIMESTAMP); 1900-01-01 00:00:00.000 2012-04-25 14:16:23.360 DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@datetime, 0); --SELECT ISNULL(@int, CURRENT_TIMESTAMP); For the first SELECT, the result is: 1900-01-01 00:00:00.000 If you uncomment the second SELECT, the batch terminates with the following error, since you can't implicitly convert a DATETIME to INT: Msg 257, Level 16, State 3, Line 3 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. ------------------------------------ While in some cases this can lead to errors, and that is usually a good thing as it allows you to correct the logic, you should also be aware about the potential for silent truncation. I consider this to be data loss without an error or any hint whatsoever that something has gone wrong. For example: DECLARE @c5 VARCHAR(5); SELECT 'COALESCE', COALESCE(@c5, 'longer name') UNION ALL SELECT 'ISNULL', ISNULL(@c5, 'longer name'); Results: COALESCE longer name ISNULL longe ---------------------------- This happens because ISNULL takes the data type of the first argument, while COALESCE inspects all of the elements and chooses the best fit (in this case, VARCHAR(11)). You can test this by performing a SELECT INTO: DECLARE @c5 VARCHAR(5); SELECT c = COALESCE(@c5, 'longer name'), i = ISNULL(@c5, 'longer name') INTO dbo.testing; SELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.testing'); Results: name system_type_id max_length is_nullable ---- -------------- ---------- ----------- c varchar 11 1 i varchar 5 0
