Tag : coalesce


Avoid Use of While Loops to get a Comma Seperated list of rows 1

Problem Detail

In the morning at your workplace, your manager or any colleague comes to you and asks to get the list of all the dates in a comma separated list.

Now the first and the quickest way of getting the output which will knock us is using a loop to get the list.

/*Getting list with While loop*/

DECLARE @fromdate DATETIME; SET @fromdate = '01-Sep-2014'

DECLARE @todate DATETIME; SET @todate = '30-Sep-2014'

DECLARE @vc_PVTColumns VARCHAR(1000); SET @vc_PVTColumns = ''

WHILE (@fromdate <= @todate)

BEGIN

SET @vc_PVTColumns = @vc_PVTColumns + '[' + CONVERT(VARCHAR(25), @fromdate, 106) + '], '

SET @fromdate = DATEADD(d, 1, @fromdate)

END

SELECT @vc_PVTColumns =LEFT(@vc_PVTColumns, LEN(@vc_PVTColumns) - 1)

SELECT @vc_PVTColumns

In above case, DB engine has to iterate for each and every value of day and do the same task again and again. The same thing can be implementedin SET based approach using COALESCE function with a … More


Be aware when using ISNULL 1

ISNULL vs COALESCE in SQL Server

In our T-SQL programming, we mostly use ISNULL function to replace the null value of a column with another value. The same can be achieved using COALESCE function too. In this blog post, we are going to see a significant difference between NULL and COALESCE functions. We are going to discuss about the implicit conversion used by ISNULL and COALESCE functions.

What is COALESCE:

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

Thus COALESCE gives a substitute value for NULL values from the given list of columns. It always returns the first NON-NULL from the list.

Syntax : – COALESCE ( expression [ ,…n ] )

COALESCE vs ISNULL

When we use ISNULL to get the substitute value for any NULL value it implicitly type casts the substitute as per the … More