Why can't I use CTEs inside transactions? 1

[tigerjade]

Anyone know why I can't use a CTE inside a transaction? Every time I try, it throws a non-sensical error message (usually complaining about a syntax error near BEGIN TRY or END TRY) but as soon as I comment out the transaction statements it's perfectly happy. I can't find anything in the MSDN documentation about this being an intended behavior (it's a feature, not a bug!) and my Google-fu on this is weak.

Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[markros]

I first googled a lot and could not find anything either, then just tested in one of my SPs and it worked fine (here is my test)

begin transaction
;WITH

-- -----------------------------------------------

-- Calcualtes sum of active taxes

-- for each of Tax Groups

-- -----------------------------------------------

Taxes as

(

  SELECT tg.nTaxGroupID             as nTaxGroupID,

         tg.cName                   as TaxGroupName,

         Sum(isNull(ti.nPercent,0)) as TaxPercent

    FROM TaxGroups                tg

    LEFT JOIN TaxGroupsToTaxItems tgi on tg.nTaxGroupID = tgi.nTaxGroupID

    LEFT JOIN TaxItems            ti  on tgi.nTaxItemID = ti.nTaxItemID

   WHERE tg.lActive = 1

   GROUP BY tg.nTaxGroupID,

            tg.cName

),

-- -----------------------------------------------

-- calculates total cost(?) per Job

-- -----------------------------------------------

JDS as

(

  SELECT nJobHeaderID,

         Sum(nAmount * nQuantity) as TotalAmount

    FROM JobDetail

   GROUP BY nJobHeaderID

),

-- -----------------------------------------------

-- concatenates all Customer's phone numbers

-- into one string (LF delimited)

-- -----------------------------------------------

P as

(

  SELECT nCustomerID,

         Stuff(PhoneInfo, 1, 1, '') AS PhoneInfo

         -- Personally I prefer Stuff for removing first few characters,

         -- but substring is O.K. too. There's no any performance advantage

         -- for one or another, as I know

         -- p.s. Substring(<exp>,2,<somebignumbereg4000>) would do the job too

         --      you don't need to calculate Len(<exp>)

    FROM ( SELECT InnerData.nCustomerID,

                  ( SELECT Char(10) + P.cPhoneNumber + ' - ' + rTrim(PT.cType)

                           -- Char(10) - LineFeed or ManualLineBreak

                           -- should do the job, probably you don't need CRLF

                      FROM PhoneNumbers   P

                     INNER JOIN PhoneType PT on P.nPhoneTypeID = PT.nPhoneTypeID

                     WHERE P.nCustomerID = InnerData.nCustomerID

                       FOR XML PATH('') -- this does string concatenation

                  ) as PhoneInfo

             FROM ( SELECT DISTINCT nCustomerID

                      FROM PhoneNumbers

                  ) as InnerData

         ) as OuterData

)          

SELECT J.nRouteHeaderID   as nRouteHeaderID, 

       JH.nCustomerID     as nCustomerID,

       JH.nJobHeaderID    as JobHeaderID,

       J.ScheduleTime     as ScheduleTime,

       JH.cContact        as Contact,

       J.SplitAmount      as SplitAmount,

       vJ.cAddr1          as Address1,

       vJ.cAddr2          as Address2,

       vJ.cCity           as City,

       vJ.cState          as State,

       vJ.cZip            as Zip,

       vJ.JobDescription  as JobDescription,

       JH.nDiscountPercent/100 * JDS.TotalAmount

                          as DiscountAmount,

       JH.nAmount         as JobTotal,

       T.TaxGroupName     as TaxType,

       T.TaxPercent       as TaxPercent,

       J.PermanentNote    as PermanentNote,

       P.PhoneInfo        as PhoneInfo

  FROM @Jobs               J

 INNER JOIN dbo.JobHeader JH  on J.nJobHeaderID   = JH.nJobHeaderID

  LEFT JOIN v_JobAddress  vJ  on J.nJobHeaderID   = vJ.nJobHeaderID

  LEFT JOIN Taxes         T   on JH.nTaxGroupID   = T.nTaxGroupID

  LEFT JOIN JDS           JDS on JH.nJobHeaderID  = JDS.nJobHeaderID

  LEFT JOIN P             P   on JH.[nCustomerID] = P.[nCustomerID]

 ORDER BY J.IdField -- Used for order but not retrieved



-- Job Details                      
select J.nJobHeaderID as JobID, JD.nQuantity as Quantity, JD.cDescription as Description, JD.nAmount as Amount
                      from @Jobs J                      
                      INNER JOIN dbo.JobDetail JD on J.nJobHeaderID = JD.nJobHeaderID
                      
 -- All notes combined
 select J.nRouteHeaderID, NN.mNote as RouteNote from @Jobs J 
 INNER JOIN (select N.nID as nCustomerID, mNote from Notes N 
 INNER JOIN NoteCategories NC on N.nNoteCategoryID = NC.nNoteCategoryID 
 and NC.cDescription = 'Customer') NN  
 ON J.nCustomerID = NN.nCustomerID
 UNION ALL
 select J.nRouteHeaderID, J.PermanentNote as RouteNote from @Jobs J 
 where J.PermanentNote IS NOT NULL and cast(J.PermanentNote as varchar(max)) <> ''
 UNION ALL
 select J.nRouteHeaderID, NN.mNote as RouteNote from @Jobs J 
 INNER JOIN (select N.nID as nJobHeaderID, mNote from Notes N 
 INNER JOIN NoteCategories NC on N.nNoteCategoryID = NC.nNoteCategoryID and NC.cDescription = 'JobHeader') NN  
 ON J.nJobHeaderID = NN.nJobHeaderID
   

commit transaction

So, may be you were doing TRY/CATCH and not the transaction?

 

[tigerjade]

Hmmm. I guess I'll go back to my code; so long as it's happy for you, that's a pretty strong indicator that it's not the TRANSACTION but a PEBCAK issue.

Thanks!

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[markros]

I was testing it on SQL Server 2005 RTM, though. May be this is a factor.

May be someone else can run a quick test too - on my local SQL Server installation I seem to have nothing to really test.

 

[mrdenny]

It works fine for me.

begin try;
	with a AS (select name from sys.objects)
	select * from a
end try
begin catch
	select ERROR_LINE()
end catch

You have to make sure to have the ; after the BEGIN TRY otherwise it won't work.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog