How to Substitute a String if record is NULL in T-SQL

Question

I'm writing a T-SQL report that shows the number of accounts that are in different statuses for different customers. The report results in something like:

Customer1    NoService        7
Customer1    IncompleteOrder  13
Customer1    NULL             9
Customer2    NoService        12
Customer2    Available        19
Customer2    NULL             3
...

The 'NULL' status is valid data, but instead of displaying NULL, I want to display "Pending". Here is my SQL so far:

USE cdwCSP;
SELECT
   sr.sales_region_name   AS SalesRegion
   , micv.value
   , COUNT(sr.sales_region_name)
FROM prospect p
   LEFT JOIN sales_region sr
     ON p.salesRegionId = sr.sales_region_number
   LEFT JOIN prospectOrder po
     ON po.prospectId = p.prospectId
   LEFT JOIN wo
     ON wo.prospectId = p.prospectId
   LEFT JOIN woTray wot
     ON wot.woId = wo.woId
   LEFT JOIN miscInformationCustomerCategory micc
     ON micc.prospectId = p.prospectId
   LEFT JOIN miscInformationCustomerValues micv
     ON micv.miscInformationCustomerCategoryId = micc.miscInformationCustomerCategoryId
   LEFT JOIN miscInformationCategory mic
     ON micc.miscInformationCategoryId = mic.miscInformationCategoryId
WHERE wot.dateOut IS NULL
     AND mic.categoryName LIKE '%Serviceability%'
GROUP BY sr.sales_region_name, micv.value
ORDER BY sr.sales_region_name, micv.value;

Any help would be appreciated, I'm still learning T-SQL so this might be an easy question to answer.

Solution 1

You can use COALESCE or ISNULL. The former is standard and returns the first NOT NULL argument (or NULL if all arguments are NULL)

SELECT COALESCE(micv.value,'Pending') as value

ISNULL is restricted to only 2 arguments but is more efficient in SQL Server if the first value to be tested is expensive to evaluate (e.g. a subquery).

One potential "gotcha" with ISNULL to be aware of is that it returns the datatype of the first parameter so if the string to be substituted is longer than the column datatype would allow you will need a cast.

E.g.

CREATE TABLE T(C VARCHAR(3) NULL);

INSERT T VALUES (NULL);

SELECT ISNULL(C,'Unknown')
FROM T

Would return Unk

But ISNULL(CAST(C as VARCHAR(7)),'Unknown') or COALESCE would both work as desired.

Solution 2

you can also use ISNULL('value', 'replacewithvalue')

Solution 3

SELECT
   sr.sales_region_name   AS SalesRegion
   , ISNULL(micv.value,'Pending')
   , COUNT(sr.sales_region_name)
FROM prospect p
--(...)

Go check ISNULL for further info.

Comments

[dvanaria]

Perfect, this works well and seems efficient. Thanks for your help.