Incorrect Syntax – With Statement

I have a query which truncates and inserts data from the stored procedure table. However, I am not able to create stored procedure since I have ;With statement after INSERT INTO table. The query is below. Can someone please help me?

CREATE PROCEDURE [BC].[TestStoredProc]
AS
BEGIN TRY
    set nocount on
    set xact_abort on
    set ansi_warnings off
    set arithabort off
    set arithignore on

BEGIN TRAN
    TRUNCATE TABLE [S].[sample]

    INSERT INTO [S].[sample] ([ColumnID])
        ;WITH P AS 
        (
            SELECT a, b, c
            FROM testtable
        )
    
    SELECT PC.columnID
    FROM ABC RC
    LEFT JOIN BCD CC ON RC.BCD = CC.BCD
    LEFT JOIN P PC ON RC.fldClientId = PC.fldPersonID
    
    
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF (XACT_STATE() <> 0)
    BEGIN
        ROLLBACK TRANSACTION
    END;

    THROW;
END CATCH

RETURN 0

Error:

Procedure [BC].[TestStorproc], Line 40 [Batch Start Line 0]
Incorrect syntax near ‘;’

Answer

The error is telling you that the CTE is defined incorrectly, you must define them before your query, that is why the ; prefix hack works, a CTE can only be the FIRST statement in a query.

Once you resolve that though, next error, is that your CTE does not have a field called fldClientId, it only has columns a,b,c…

I suspect you have over-simplified your example, for the rest of this solution lets assume that P has a fldClientId

Move the CTE outside of the INSERT:

;With P as (
    SELECT a, b, c, fldClientId
    FROM testtable
)
INSERT INTO [S].[sample]([ColumnID])
SELECT PC.columnID
FROM ABC RC
LEFT JOIN BCD CC on RC.BCD = CC.BCD
LEFT JOIN P PC on RC.fldClientId = PC.fldPersonID

However, your CTE is so simple, it’s just a column projection, and you’re not re-using it in the query, so you can transpose that inline:

INSERT INTO [S].[sample]([ColumnID])
SELECT PC.columnID
FROM ABC RC
LEFT JOIN BCD CC on RC.BCD = CC.BCD
LEFT JOIN (
    SELECT a, b, c, fldClientId 
    FROM testtable
) AS PC on RC.fldClientId = PC.fldPersonID

Which again can be further simplified to:

INSERT INTO [S].[sample]([ColumnID])
SELECT PC.columnID
FROM ABC RC
LEFT JOIN BCD CC on RC.BCD = CC.BCD
LEFT JOIN testtable AS PC on RC.fldClientId = PC.fldPersonID