Hey, VS 2008 shipped 2 days ago... so in honor of that I'm going to talk about something that shipped with the last version of SQL Server (SQL 2005).
I recently discovered CTEs via Eric. You may want to read one of the following articles to learn about them: John Papa or Scott Mitchell.
I have begun to use them within Stored Procs, but they seem to be very finicky as to where they go. witin the proc.
This is what I discovered. They work great but you can use one at a time. That means that you can use a CTE to define another CTE (you are effectively using the first CTE).
If you try to use a second CTE without using the initially created one you will get an error.
Also try something like this (with a real table and a real field):
1: ;WITH myCTE(AccountID) AS
2: (
3: SELECT RealField
4: FROM RealTable
5: )
6:
7: SELECT TOP 10 *
8: FROM myCTE
9:
10: SELECT TOP 100 *
11: FROM myCTE
You will get an error on that second statement. Why? Well I believe that CTEs are tied to the next SQL statement. You will actually get an error if the next SQL statement doesn't use the CTE.
So to use CTEs with the above statement you have to do something like this.
1: ;WITH myCTE(AccountID) AS
2: (
3: SELECT RealField
4: FROM RealTable
5: )
6:
7: SELECT TOP 10 *
8: FROM myCTE
9:
10: -- CTE is only used once... so if we want to use it again we have to define it again
11: ;WITH myCTE(AccountID) AS
12: (
13: SELECT RealField
14: FROM RealTable
15: )
16:
17: SELECT TOP 100 *
18: FROM myCTE
I would not call the above an ideal scenario. CTEs definitely make your code more readable, but defining them multiple times doesn't seem like a great solutions. I would say you would be better served with a View, Table function or something like that.
Scott Mitchell mentions some advice in his article that I would wholeheartedly agree with: "Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system."
Print | posted on Wednesday, November 21, 2007 7:12 AM