[RESOLVED] Explain what is happening here... Please

Hi

Finally, some (possibly silly) people have let me get my mits on MS Sql Server databases.
Where, with only the experience of MS Access and examples (previously) from here and online, I get to wade in with big boots.

Ok so the basics are the same for querying, table structures are a little different and sub queries tend not to do as I thought. However ...


Yesterday I wanted to get a list of dates thirty (30) days in the past to now. But I don't want to create a table, at least not in the sense of writing; storing and populating it. So I searched online and found a chunk of code. After discussing here with a colleague I ended up with the SQL below.

But how does it work.?
I get the gist, start date, loop each day, it's just how it does it without an explicit loop...

Could some one please answer this really easy question??

Code:

---

with T(day) as
(
    select DATEADD(dd,-30,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)) as day
        union all
    select day + 1
        from T
        where day < DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
)
select * from T

---

It is what's called a CTE - Computed Table Expression ... Additionally it is a recursive CTE. They are quite powerful. I use them to walk heirarchies and XML sometimes.

You'll notice the "from T" inside of it ... that makes it recursive... the first part of the union marks the "anchor" the second part is the recursive part, it selects from the T table (which initially has 1 record, the anchor) and then adds to it, and around and around it goes until the end condition is met and the whole thing is returned.

Google "MSDN recursive CTE" ... the MSDN entry on them should be the first link or two returned. It goes into a bit more (better) explanation.

-tg

The loop is standard T-SQL - from before CTE's.

Here is an example - paste this into a QUERY window in SSMS and EXECUTE it...

Code:

---

Create Table #Test (TestField varchar(1))

Insert into #Test values ('A')
Insert into #Test values ('B')
Insert into #Test values ('C')
Insert into #Test values ('D')
Insert into #Test values ('E')

Declare @TestVbl varchar(100)

Select @TestVbl=IsNull(@TestVbl+', ','')+TestField
        From #Test
        Order by TestField Desc

Select @TestVbl

---

This is the result of running the script

Code:

---

-------------------------
E, D, C, B, A

(1 row(s) affected)

---

This SELECT is in affect a LOOP - "Select @TestVbl=IsNull(@TestVbl+', ','')+TestField..." - as it's going to run through all the rows in the table that match the criteria.

If you are new to MS SQL one of the best things you will discover is STORED PROCEDURE's where you can have queries and variables and all kinds of IF/blocks and loops and do big stuff in one "function call" basically.

Quote:

---

Originally Posted by techgnome

It is what's called a CTE - Computed Table Expression ... Additionally it is a recursive CTE. They are quite powerful. I use them to walk heirarchies and XML sometimes.

You'll notice the "from T" inside of it ... that makes it recursive... the first part of the union marks the "anchor" the second part is the recursive part, it selects from the T table (which initially has 1 record, the anchor) and then adds to it, and around and around it goes until the end condition is met and the whole thing is returned.

Google "MSDN recursive CTE" ... the MSDN entry on them should be the first link or two returned. It goes into a bit more (better) explanation.

-tg

---

CTE
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Excellent. Thank you all for the info!