Syntax error in sproc with CTE inside a Try and Transaction block

Question

Using SQL Server 2019 CU23 and SSMS 19.2.
The update statement is the only custom part of this sproc; the rest is provided by the template which I'm not supposed to change (there can be multiple DML statements in the general case).

By itself, the update statement works, and does the right thing. But within this template it causes:

Msg 102, Level 15, State 1, Procedure tblJobDetails_StoneLocal, Line 11 [Batch Start Line 0]
Incorrect syntax near 'cteSlabCounts'.

In SQL Server database project I got the even more unhelpful: Error SQL46005: Expected MARK but encountered cteSlabCounts instead.

Can you help me understand why this error? Are CTEs illegal within a Try and Transaction block?

CREATE PROCEDURE [dbo].[tblJobDetails_StoneLocal](
@JobID int
, @StoneID int
)
as
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION

			with cteSlabCounts(JobID, StoneID, CountTotal, Count_O_X, Count_R_@X) as (
				select JobID
					, StoneID
					, CountTotal = count(sd.SlabDetailID)
					, Count_O_X = sum(case when sd.OrderStage = 'X' then 1 else 0 end)
					, Count_R_@X = sum(case when sd.Received in ('@', 'X') then 1 else 0 end)
				from dbo.tblSlabDetails sd
				group by JobID, StoneID
			)
			update dbo.tblJobDetails
			set StoneLocal = 1
				from dbo.tblJobDetails jd
				inner join cteSlabCounts sc on sc.JobID = jd.JobID and sc.StoneID = jd.CounterTopID
				where sc.JobID = @JobID and sc.StoneID = @StoneID and sc.Count_O_X = sc.CountTotal and sc.Count_R_@X = sc.CountTotal

		COMMIT TRANSACTION
		
		--Return the affected record(s)?
		select JobDetailID from dbo.tblJobDetails where JobID = @JobID and CounterTopID = @StoneID;
		--return 0;

	END TRY
	BEGIN CATCH	
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

		--OMITTED: Write Exception to Trace.

		THROW
	END CATCH

END

Answer 1

Hi @Tom van Stiphout

Try adding a semicolon before WITH. ;with cteSlabCounts(JobID, StoneID, CountTotal, Count_O_X, Count_R_@X) as

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The last command before the WITH statement (here BEGIN TRANS) must end with a semicolon, that's mandattory, see

Guidelines for creating and using common table expressions =>

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.