Thursday, December 20, 2012

How to get date range in a list table using T-SQL (sql server)

How to get date range in a list table using T-SQL (sql server)


declare @a as datetime

declare @b as datetime

set @a = '2012-02-21'
set @b = '2012-09-11';


with cte(A) as
(
select @a A
union all
select DateAdd(day,1,A) A from cte where A<@b
)

SELECT * FROM cte
option (maxrecursion 3660);

Wednesday, December 5, 2012

T-SQL concatenate large string very slow - solution

Credits : Darko Ilić-Šikelj


you have a table with tens of thousands of rows or more, and you want to combine the row contents into a single string


declare @n as nvarchar(max)
set @n = ''

select @n = @n + word from myTable

takes bloody long time

solution


declare @n as nvarchar(max)
set @n =
STUFF(
       

     select word + '' from myTable
     FOR XML PATH('') 
    )  
, 1, 1, '' ) 


voila ! problem solved. very quick. notice the code in lime-green. if you omit that, the results will be in xml node format , if u add that empty string, or 1 space string, it will be just a string without any xml formatting