September 25, 2015 at 12:33 am
An annoyance for me is that a CTE doesn't seem to allow me to highlight-a-section-and-execute as I can with a SQL statement without a CTE.
For example, if I have
SELECTCol1,
Col2,
... long list of columns ...
-- SELECT COUNT(*)
-- [highlight="#FFFF00"]SELECT TOP 10 *
FROMMyTable
JOIN SomeOtherTables
...[/highlight]
I can select, from the bottom up, to include one of the commented out SELECT statements. Maybe I'm alone in this approach! but I do this A LOT.
Delete example:
DELETE D
-- SELECT COUNT(*)
-- [highlight="#FFFF00"]SELECT TOP 10 *
FROMMyTable AS D
JOIN SomeOtherTables
...[/highlight]
Update example:
UPDATE U
SET
-- [highlight="#FFFF00"]SELECT TOP 100 Col1, Col2,
Col3 = 'Foo'
-- SELECT COUNT(*)
-- SELECT TOP 10 *
FROMMyTable AS U
JOIN SomeOtherTables
...[/highlight]
With CTE I haven't been able to find a similar method, other than having to comment-out and back in again the whole of the SELECT section - which itself may contain /* */ comments 🙁
;WITH CTE AS
(
SELECTID, Col1, Col2
FROMMyTable
WHERE...
)
/* possible comment */
SELECTCol1,
Col2,
/* possible comment */
... long list of columns ...
-- SELECT COUNT(*)
-- SELECT TOP 10 *
FROMCTE AS C
JOIN MyTable AS T
ON T.ID = C.ID
...
any suggestions please? Anyone else do this, or am I the only one?!!
September 25, 2015 at 3:35 am
I combine -- and /* to make it a bit easier but still have to edit code.
WITH CTE(x) AS (
SELECT NULL
)
/* possible comment */
-- /*
SELECT x=1,
/* possible comment */
y=2
-- */ SELECT cnt= COUNT(*)
-- SELECT z=3
FROM CTE
WITH CTE(x) AS (
SELECT NULL
)
/* possible comment */
/* --
SELECT x=1,
/* possible comment */
y=2
-- */ SELECT cnt= COUNT(*)
-- SELECT z=3
FROM CTE
WITH CTE(x) AS (
SELECT NULL
)
/* possible comment */
/* --
SELECT x=1,
/* possible comment */
y=2
-- SELECT cnt= COUNT(*)
-- */ SELECT z=3
FROM CTE
September 27, 2015 at 4:09 am
-- /*
... Live code that optionally needs disabling ...
-- */
is interesting, thanks. I saw that style for the first time just the other day on some sample code in a post here but didn't extrapolate the significance.
For optional code I normally do
/**DEBUG *
... Optional Debug code ...
*DEBUG **/
the single/double stars are deliberate so I can remember which end I need to add/remove the "/" later on! i.e. the double-star end is retained
Comment-out block activated:
/**DEBUG *[highlight="#ffff11"]/[/highlight]
... Optional Debug code ...
[highlight="#ffff11"]/[/highlight]*DEBUG **/
Whilst on the subject, I have another workaround - which folk may think is shoddy? or have a better suggestion?
zzzz Comment out for TEST / PRODUCTION
/**DEV ONLY *[highlight="#ffff11"]/[/highlight]
... some DEV code ...
[highlight="#ffff11"]/[/highlight]*DEV ONLY **/
The "zzzz" triggers a syntax error, so I have to deal with the comment. When addressed I change the comment to "--xxzzzz". If that gets saved into the Source Code file it will be picked up when I (do a DIFF compare when I) check-in the file, and I will change it back to "zzzz" at that time.
At "release" to QA we concatenate all relevant source code files (every SProc, Trigger, View etc.) into a single release-script, search for "ZZZZ" and find & fix the comment appropriately for Release.
Oh! for a pre-processor directive 🙂
April 19, 2016 at 12:21 am
I always re-factor the CTE's when debugging them, for simplicity
;WITH CTE AS
(
SELECTID, Col1, Col2
FROMMyTable
WHERE...
)
SELECTCol1,
Col2
FROMCTE AS C
JOIN MyTable AS T
ON T.ID = C.ID
SELECTCol1
, Col2
FROM(
SELECTID, Col1, Col2 --Old CTE used in a subquery instead.
FROMMyTable
WHERE...
) AS C
JOIN MyTable AS T
ON T.ID = C.ID
April 19, 2016 at 6:50 am
dave.jones 96182 (4/19/2016)
I always re-factor the CTE's when debugging them, for simplicity;WITH CTE AS
(
SELECTID, Col1, Col2
FROMMyTable
WHERE...
)
SELECTCol1,
Col2
FROMCTE AS C
JOIN MyTable AS T
ON T.ID = C.ID
SELECTCol1
, Col2
FROM(
SELECTID, Col1, Col2 --Old CTE used in a subquery instead.
FROMMyTable
WHERE...
) AS C
JOIN MyTable AS T
ON T.ID = C.ID
I can't see any additional simplicity there – your queries are identical. Maybe that's because I am used to seeing CTEs.
I'd like to see your attempts to debug a recursive CTE using this method.
Note that semicolons act as statement terminators, not statement initiators.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply