How to SET the local variable value equal to result from CTE table?

Question

I have a question We have a table HR like this

EmpName    Empmanager
Jack        Jane
Tom         Jane 
Tony        Victor
Isabella    Hugo
Anna        Victor

So I want to extract the DISTINCT emp.manager then select them by line and give them to a variable suppose mgrname. I got error at SET @mgrname syntax?

DECLARE @count INT
DECLARE @mgrname VARCHAR(20)
SET @count = 3
WITH  MGRlist as(
    SELECT DISTINCT Empmanager FROM HR)
    **SET @mgrname** = SELECT Empmanager  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY mgr ASC) AS RowNumber,
    *
    FROM MGRlist
    ) AS RESULT
    WHERE RowNumber = @count -- FIND THE CURRENT MANAGER NAME

Answer 1

If you want to get the distinct manager name in that case you don't need CTE.

You are trying to insert multiple values in a VARCHAR data type

DECLARE @mgrname VARCHAR(20)

Instead of creating a VARCHAR variable, you should be creating a TABLE type variable which can hold multiple values like following.

DECLARE @mgrname TABLE(ManagerName VARCHAR(100))

To fetch specific count of distinct manager names you can use

SELECT DISTINCT TOP (@count)

Your final query should look like following.

DECLARE @count INT
DECLARE @mgrname table (ManagerName VARCHAR(100))
SET @count = 3

INSERT INTO @mgrname
SELECT DISTINCT TOP (@count)  Empmanager FROM HR
ORDER BY Empmanager
--Now output is stored inside a table variable.
SELECT * FROM @mgrname