How to call a CTE in a follow up IF statement-CodePudding

I know that a CTE must be followed by a SELECT, INSERT, UPDATE or DELETE statement.

So how should I use this kind of logic in my procedure?

CREATE PROCEDURE st_exemple
AS
    SET NOCOUNT ON
    (...)

    WITH CTE AS 
    (
        SELECT 
            'ID COTA'   = TABEL1.ID_COTA,
            'NM CLIENT' = TABLE2.NM_CLIENT
        FROM 
            (...)
        WHERE
            (...)
    );

    IF (SELECT COUNT(*) FROM CTE) = 0
    BEGIN
        SET @COMMENT = 'SELECT WTIHOUT DATA'
        GOTO END_PROC
    END;
    ELSE
    BEGIN
        SET @COMMENT = 'SELECT WITH DATA';
        GOTO END_PROC
    END;

I want to create a variable with the observation of what happened with the execution of the procedure, so I have a kind of a log of the executions in another table.

Since the procedure will be executed every day to create a .txt but not every day will be available data in the where clause, I want to keep a log so the BU won't think it's an error

CodePudding user response：

How to call a CTE in a follow up IF statement

You can't, use a temp table instead, especially if you need re-usability or are trying to go for readability:

CREATE PROC st_exemple
AS
(...)

DROP TABLE IF EXISTS #Results;
SELECT 
    TABEL1.ID_COTA,
    TABLE2.NM_CLIENT
INTO #Results   
FROM (...)
WHERE(...);

IF EXISTS (SELECT * FROM #Results)
  BEGIN
    SET @COMMENT = 'SELECT WTIHOUT DATA'
    GOTO END_PROC
  END;
ELSE
  BEGIN
      SET @COMMENT = 'SELECT WITH DATA';
      GOTO END_PROC
  END;