How to populate a variable using a CTE

The following table called t1 is used for the examples:
 
image1
 
If the CTE (Common Table Expression) returns a single value then you could use:

--variable
DECLARE @v char(1);

--CTE
;WITH CTE
AS
(
	SELECT c2
	FROM t1
	WHERE c1=2
)
SELECT @v = c2
FROM CTE;

--verify correct
SELECT @v AS the_value;

image2
If the CTE will return multiple values, and say you only want the first descending ordered one, then you could use:

--variable
DECLARE @v char(1);

--CTE
;WITH CTE
AS
(
	SELECT c2
	FROM t1	
)
SELECT TOP 1 @v = c2
FROM CTE
ORDER BY c2 DESC;

--verify correct
SELECT @v AS the_value;

image3
 
You can also store CTE values into table variables, but I’ll cover that in another post.

Leave a Reply