Hardik Parmar Hardik Parmar - 5 months ago 10
SQL Question

How Stuff Works Or Logic of Stuff & XML Path in SQL

My Output is correct but I want to know how actually STUFF works.

I have the simple query which returns me total number of months between


I am storing that months into the the @cols by help of STUFF.

Query is like this :

+ Quotename(CONVERT(CHAR(10), startdate, 120))
FROM #tempdates

  1. "," in the query should print before the values but it print like below O/P.

  2. If I remove XML Path from the stuff I am getting null value.

  3. How actually STUFFworks with XML path

Here's My output :

enter image description here

@startdate datetime = '1-Jan-2014',
@enddate datetime = '1-Jun-2014'

;with cte (StartDate, EndDate) as
select min(@startdate) StartDate, max(@enddate) EndDate
union all
select dateadd(mm, 1, StartDate), EndDate
from cte
where StartDate < EndDate
select StartDate
into #tempDates
from cte

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), StartDate, 120))
from #tempDates
).value('.', 'NVARCHAR(MAX)')

select @cols
drop table #tempDates


Stuff works on strings, the only thing its doing on your SQL is remove the initial comma from position 1. Without stuff it would look like ,a,b,c,d but when you stuff the position one with an empty value it transforms it to a,b,c,d

Your question is probably more about what FOR XML is doing. In this case, the FOR XML is being applied as a "trick" to concatenate all the rows from #tempDates in one long comma-separated string ,a,b,c,d and stuff is just removing that first comma.

For xml is creating a string x = ',a' + ',b' +',c' + ',d' so x ends up as ',a,b,c,d'

Stuff(x,1,1,'') is replacing only the comma in position 1 to 1 with '' therefore is now x='a,b,c,d'

[STUFF ( character_expression , start , length , replaceWith_expression )]