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)


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

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


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 table which holdspre-filled dates for some period.

Imagine we have a table named cmn_Dates, which holds the dates for some predefined periods. Using that table we can write a query like below;

/*Getting same list with COALESCEfunction*/

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

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

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

SELECT @vc_PVTColumns =COALESCE(@vc_PVTColumns, '')

+ '[' + CONVERT(VARCHAR(25), dt_Date, 106) + '], '

FROM cmn_Dates

WHERE dt_DateBETWEEN @fromdateAND @todate


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

SELECT @vc_PVTColumns

cmn_Dates table structure:





Conclusion: –

If you are thinking that why we will create a table and insert all the dates for some given periods (10 years or 20 years), which will take more resources as well as time and the same can be achieved using loop in a very simple way. Then you are absolutely right. But in case we have to execute the similar type of queries through out our application and at very short period like 5 seconds or 10 seconds. Then the SET based approach will give far better performance in comparison of loop.

Rate This
[Total: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Avoid Use of While Loops to get a Comma Seperated list of rows