I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.
Option 1 :
/* Method 1 */ ;WITH CTE1 AS (SELECT 1 AS Col1), CTE2 AS (SELECT 2 AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
Option 2:
/* Method 2 */ ;WITH CTE1 AS (SELECT 1 AS Col1), CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.
Rules to Remember for Multiple CTE in One Query
The syntax is simple once you see it: one WITH keyword at the start, then each definition separated by a comma. You do not repeat WITH for the second one. That is the most common mistake I see, and it gives a syntax error that confuses many beginners.
The order also matters. A later expression can read from an earlier one, like the second example does, but an earlier one cannot read from one defined after it. Every name in the list must be unique.
The leading semicolon in the examples is there for a reason. WITH has other meanings in T-SQL, so when a CTE is not the first statement in the batch, the statement before it must end with a semicolon. Putting it at the start is a simple habit that avoids the error when someone adds code above yours.
A few more things that often surprise people:
- The scope is one statement only. The next query after the
SELECTcannot see these names. - SQL Server does not store the result. If you reference the same expression twice, its query can run twice. For heavy logic used many times, a temp table may be faster.
- You can use them with
INSERT,UPDATEandDELETEtoo, not only withSELECT.
Used well, a chain of small named steps reads like a recipe, and that makes a long query much easier to debug later. Give each step a name that says what it holds, like ActiveCustomers or MonthlyTotals, and the query almost explains itself. It also helps the next developer who opens your code.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
73 Comments. Leave new
Do we have WITH clause in SQL Server 2008 R2
can i fire query like this in sql server 2008
Select * from emp;
please suggest me i am new to sql server 2008.
thank you again…
Use SELECT TOP 99.99999999999 PERCENT * FROM….. It worked for me…. It gives the same COUNT comparing with simple SELECT * FROM statement….
Looking all over for this answer and this comes close, how do I incorporate this CTE:
SELECT TarnScore.CGCODINGMASTEROID, TarnScore.Score
AS “TARN Score”
FROM (
SELECT DISTINCT ST2.CGCODINGMASTEROID,
(
SELECT SUM(ted.TARNSCORE)
FROM HealthBI.dbo.LZO_CGCODINGDETAIL AS ST1
LEFT join CommDB.dbo.tblTARNEDCodes as ted on ST1.DESCRIPTION = ted.DESCRIPTION
WHERE ST1.CGCODINGMASTEROID = ST2.CGCODINGMASTEROID
AND ted.[TARN Possible] ‘None’
—ORDER BY ST1.CGCODINGMASTEROID
) [Score]
FROM HealthBI.dbo.LZO_CGCODINGDETAIL ST2
) AS [TarnScore]
Into this SQL Code (note its the final bit where this look similar and the join would occur I think)
;WITH CTE
AS (
SELECT ed.AttendDateTime AS [ED Attend Date],
ip.HEYNo AS [HEY Number],
ip.NHSNo1 AS [NHS Number],
ip.Forename,
ip.Surname,
ip.DOB,
ed.ComplaintFreetext AS [Additional Comments],
ed.LOCAL_ACCIDENT_AND_EMERGENCY_DIAGNOSIS_DESCRIPTION,
ed.StreamDesc AS [Major/Minor/Paeds],
CASE WHEN ip.DateOfDeath IS NULL THEN ‘N’ ELSE ‘Y’ END AS [Died In Hospital],
ed.DepartDateTime,
ip.AdmitDateTime,
CASE WHEN ip.DischDateTime IS NULL THEN ‘N’ ELSE ‘Y’ END AS [Discharged?],
ip.DischDateTime AS [Discharge Date/Time],
ed.EDAttendanceOID,
ce.IDENTIFYINGOID,
ce.IDENTIFYINGTYPE,
cm.OID AS [CodingMasterOID],
e.OID AS [EncounterOID]
FROM dbo.ED_ATTENDANCE AS ed
LEFT JOIN dbo.IP_ADMISSION AS ip ON ed.MFPatientID = ip.MFPatientID
INNER JOIN HealthBI.dbo.LZO_AEATTENDANCE AS lae ON ed.EDAttendanceOID = lae.OID
INNER JOIN HealthBI.dbo.LZO_ENCOUNTER AS e ON lae.ENCOUNTEROID = e.OID
INNER JOIN HealthBI.dbo.LZO_PATIENTCAREEVENTS AS ce ON e.OID = ce.ENCOUNTEROID
AND ce.IDENTIFYINGTYPE = ‘Create Attendence’
LEFT JOIN HealthBI.dbo.LZO_CGCODINGMASTER AS cm ON ce.IDENTIFYINGOID = cm.IDENTIFYINGOID
AND ce.IDENTIFYINGTYPE = cm.IDENTIFYINGTYPE
—- ok to here
WHERE ed.AttendDate >= ’16 january 2016′
AND ed.AttendDate < '17 january 2016'
AND (DATEDIFF(HOUR, ed.DepartDateTime, ip.AdmitDateTime) BETWEEN – 5 AND 5)
AND (ed.HospitalCode = 'RWA01')
)
—————————————-
— below concatinates multiple rows/fields into one long string
SELECT Main.CGCODINGMASTEROID,
Left(Main.DESCRIPTIONS, Len(Main.DESCRIPTIONS) – 1) AS "ED Descriptions",
c.*
FROM (SELECT DISTINCT ST2.CGCODINGMASTEROID,
(SELECT ST1.DESCRIPTION + ', ' AS [text()]
FROM HealthBI.dbo.LZO_CGCODINGDETAIL AS ST1
LEFT join CommDB.dbo.tblTARNEDCodes as ted on ST1.DESCRIPTION = ted.DESCRIPTION
WHERE ST1.CGCODINGMASTEROID = ST2.CGCODINGMASTEROID
AND ted.[TARN Possible] ‘None’
ORDER BY ST1.CGCODINGMASTEROID
FOR XML PATH(”)
) [DESCRIPTIONS]
FROM HealthBI.dbo.LZO_CGCODINGDETAIL ST2
) AS [Main]
RIGHT JOIN CTE AS c ON Main.CGCODINGMASTEROID = c.CodingMasterOID
I need the SQLserver 2005 program or query for the below mentioned table (date diff)
SEQ1 PRCS_DT SEQ2
1 2012-07-22 0
2 2012-07-23 1
3 2012-07-24 1
4 2012-07-26 2
5 2012-07-29 3
I have a copy of it
How do I convert a string to number in a store procedure that uses an open query
You use a function to convert the string to a number