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

Question

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 @startDate & @endDate.

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

Query is like this :

SELECT DISTINCT ',' 
                + 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 :

 DECLARE @cols AS NVARCHAR(MAX),   
            @query AS NVARCHAR(MAX),  
            @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   
        FOR XML PATH(''), TYPE   
        ).value('.', 'NVARCHAR(MAX)')   
        ,1,1,'') 


        select @cols
        drop table #tempDates

Answer 1

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 )]