Using If Exists with a Cte

Using Common Table Expression and IF EXISTS

I really doubt, that this is the best approach... You tried to clean and shorten this for brevitiy (thumbs up for this!), but the given information is - maybe - not enough.

You cannot use a CTE in different queries. A CTE is fully inlined as part of the query...

But you could write your values into a table variable like here:

DECLARE @tbl TABLE(Column1 INT, Column2 VARCHAR(100)); --Choose appropriate types
INSERT INTO @tbl  
SELECT ColumnA, ColumnB FROM SomeTable WHERE ColumnA=SomeValue;

This table variable can be used in later queries (but in the same job!) like any other table:

SELECT *
FROM SomeTable AS st
INNER JOIN @tbl AS tbl ON ...

... or similiar usages...

Another approach might be this

SELECT Column1,Column2 INTO #SomeTempTable FROM SomeWhere

This will write the result of the SELECT into a temp table (which is session wide).

I'm quite sure, that there might be a better (set-based) approach... Are the two sub-queries identical in their result set's structure? If so, you might use UNION ALL and place your "IF EXISTS" as a WHERE-clause to each sub query.

How can I use if statement after a CTE (SQL Server 2005)

Common table expressions are defined within the context of a single statement:

WITH cte_name AS (
  <cte definition>)
<statement that uses cte>;

So you can do something like:

WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
SELECT * FROM CTE;

WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
UPDATE CTE 
SET somefield = somevalue
WHERE id = somekey;

A CTE must be followed by a single
SELECT, INSERT, UPDATE, MERGE, or
DELETE statement that references some
or all the CTE columns. A CTE can also
be specified in a CREATE VIEW
statement as part of the defining
SELECT statement of the view

Select into variables from cte using if oracle

In your code:

-- Start of first SELECT statement.
WITH test_cte AS (
  SELECT *
  from(
    SELECT date_a,lastName,firstName,birthDate, rank() over(ORDER BY date_a desc) rnk
    FROM test_table a
    join test_table b ON b.id = a.bid 
  )a1
  WHERE rnk =1
)
SELECT count(*) into count_a 
FROM test_table a
join test_table b ON b.id = a.bid
WHERE a.code = code_name;
-- End of first SELECT statement.

IF count_a > 0 THEN
  -- Start of second SELECT statement.
  SELECT date_a,lastName,firstName,birthDate
  into date_a_var,lastName_var,firstName_var,birthDate_var
  FROM test_cte;
  -- End of second SELECT statement.
ELSE 
  date_a_var := NULL;
  lastName_var := NULL;
  firstName_var := NULL;
  birthDate_var := NULL;
END IF;

It won't work because the test_cte only exists for the one statement and when you finish the statement's final SELECT statement then it no longer exists for the subsequent statements. (However, you do not use the sub-query factoring clause [a.k.a. CTE] in the SELECT for that statement so it is not clear why you need the WITH clause.)

Instead of trying to use COUNT, just get the data and handle the NO_DATA_FOUND exception if it occurs (also, from Oracle 12, you don't need to use RANK and can use FETCH FIRST ROW WITH TIES instead):

DECLARE
  date_a_var    test_table.date_a%TYPE;
  lastName_var  test_table.lastname%TYPE;
  firstName_var test_table.firstname%TYPE;
  birthDate_var test_table.birthdate%TYPE;
BEGIN
  BEGIN
    SELECT date_a,
           lastName,
           firstName,
           birthDate
    INTO   date_a_var,
           lastName_var,
           firstName_var,
           birthDate_var
    FROM   test_table a
           join test_table b ON b.id = a.bid 
    ORDER BY date_a DESC
    FETCH FIRST ROW WITH TIES;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      date_a_var    := NULL;
      lastName_var  := NULL;
      firstName_var := NULL;
      birthDate_var := NULL;
  END;

  -- Continue processing
END;

(Note: You may also get a TOO_MANY_ROWS exception if there are duplicate dates. Either use FETCH FIRST ROW ONLY or, before Oracle 12, the ROW_NUMBER analytic function.)

Recursive CTE query in EXISTS

First find all the roles down the hierachy from the given one then select users taking any of the roles found.

WITH RECURSIVE cte AS (
   -- The role and its descendants 
   SELECT * 
   FROM roles 
   WHERE name = 'superuser'
   UNION ALL
   SELECT roles.* 
   FROM roles 
   JOIN cte ON cte.id = roles.parent_id 
)
SELECT DISTINCT u.* 
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN cte r ON r.id = ur.role_id

db<>fiddle

Recursive CTE check if record exists at any level then get all children (folder structure)

The query below tries first to list folders the user has access to then the required hierarchy among accessible folders

DECLARE @UserId INT = 1;
DECLARE @FolderId INT = 5;

WITH
AccessibleHierarchy (Id, ParentId , Name) AS
(
    SELECT fold.Id, fold.ParentId, fold.Name FROM Folders fold 
    WHERE fold.[CreatorUserId] = @UserId or fold.ID in (select [FolderId] from [FolderAccess] where [UserId] = @UserId)
    UNION ALL
    SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
    INNER JOIN AccessibleHierarchy cte on cte.Id = parents.ParentId
),
QueriedHierarchy (Id, ParentId , Name) AS
(
    SELECT distinct fold.Id, fold.ParentId, fold.Name FROM AccessibleHierarchy fold 
    WHERE fold.ID = @FolderId
    UNION ALL
    SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
    INNER JOIN QueriedHierarchy cte on cte.Id = parents.ParentId
)
select * from QueriedHierarchy