How to include a variable in a block WITH..AS?

I have this query to retrieve for each employee, his main and secondary function:

DECLARE @mainFunctionId INT

SELECT TOP 1 
    @mainFunctionId = FunctionId 
FROM 
    (SELECT 
         [le].[EmployeeId], [le].[FunctionId],[le].[Label], 
         ROW_NUMBER() OVER (PARTITION BY [le].[EmployeeId] ORDER BY [le].[SortOrder] ASC) AS [RealOrder] 
     FROM 
         (SELECT 
              [e].[EmployeeId], [es].[FunctionId], ef.Label, ISNULL([es].[SortOrder], 9999) AS [SortOrder]
          FROM 
              [Employee] [e] 
          INNER JOIN 
              [Employee_Scope] AS [es] ON [es].[EmployeeId] = [e].[EmployeeId] 
          INNER JOIN
              [Employee_Function] AS [ef] ON es.FunctionId = ef.FunctionId) [le]) [emp]

SELECT DISTINCT 
    [emp].[EmployeeId], [emp].[FunctionId],[emp].Label, IIF(@mainFunctionId = emp.FunctionId,'main','secondary')
FROM 
    (SELECT 
         [le].[EmployeeId], [le].[FunctionId], [le].[Label], 
         ROW_NUMBER() OVER(PARTITION BY [le].[EmployeeId] ORDER BY [le].[SortOrder] ASC) AS [RealOrder] 
     FROM 
         (SELECT 
              [e].[EmployeeId], [es].[FunctionId],ef.Label, ISNULL([es].[SortOrder], 9999) AS [SortOrder]
          FROM 
              [Employee] [e] 
          INNER JOIN 
              [Employee_Scope] AS [es] ON [es].[EmployeeId] = [e].[EmployeeId] 
          INNER JOIN 
              [Employee_Function] AS [ef] ON es.FunctionId = ef.FunctionId) [le]) [emp]

Output :

I want to integrate my previous query in the source query of my dimension :

SELECT 
    [E].[EmployeeId], [E].[AdminFileId], [E].[Lastname] + ' ' + [E].[Firstname] AS [Name], [ES][Status]
FROM 
    [Employee] AS [E] 
INNER JOIN 
    [EmployeeStatus] AS [ES] ON [ES].[EmployeeStatusId] = [E].[EmployeeStatusId]

My expected output will be (for employee having EmployeeId=1) :

EmployeeId  AdminFileId  Name       Status Label                     
-----------------------------------------------------------------------
1           23544        Marco Polo IN     Manager        main 
1           23544        Marco Polo IN     Senior  Account secondary 
1           23544        Marco Polo IN     Office Manager secondary

Answer

When you say:

How to include a variable in a block

are you asking how can you use @mainFunctionId for each employee? And perhaps when you mention “with…as” are you hinting that you feel a CTE is perhaps the way to go?

If so, I would get away from the variable mindset altogether. Also, in your first query, you don’t reveal what you want to order by to get the top item. But since you have the ‘RealOrder’ column in it, I’m guessing that is your intention.

Below I’ve simplified your second query. “Distinct” is negated by your use of row_number. There is no need for it, or, if there is, you’ve got to rework your query. The nesting of the subqueries to produce the isnull around the sort order is not needed. You can be more direct and just create that in the row_number function. Other simplifications are more just a matter of style.

After the simplifications, I simply wrapped the core query into a CTE, and from the referencing query I made use of the previously calculated realOrder column to identify main and secondary records.

with

    employeeScopeFunctions as (

        select      e.employeeId, 
                    es.FunctionId,
                    ef.Label, 

                    realOrder = row_number() over(
                        partition by le.employeeId
                        order by isnull(es.sortOrder, 9999)
                    )

        from        employee e 
        join        employee_scope es on es.employeeId = e.employeeId
        join        employee_function ef on es.FunctionId = ef.FunctionId

    )

    select      *,
                primacy = iif(realOrder = 1, 'main', 'secondary')
    from        employeeScopeFunctions