How to Set a variable inside a Function with Select SQL-CodePudding

I need to make a function that returns a table from employees where they work under a specific employee.

I tried to write the following but I'm getting error:

An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

CREATE FUNCTION GetSubTree (@SupervisorID int)
RETURNS @Result TABLE(
    Id  INT     PRIMARY KEY NOT NULL,
    HIERARCHY   HIERARCHYID    NOT NULL ,
    FirstName   NVARCHAR(50)    NOT NULL,
    LastName   NVARCHAR(50)    NOT NULL,
    NationalCode    bigint    NOT NULL,
    EmployeeRole    NVARCHAR(3) NOT NULL
)
AS
BEGIN
    DECLARE @hi HIERARCHYID;
    DECLARE @hiT TABLE(
        HI HIERARCHYID
    )
    INSERT into @hiT
    SELECT Top 1 @hi= HIERARCHY
    FROM dbo.Company as C1
    WHERE C1.Id = @SupervisorID;
    INSERT INTO @Result
    SELECT * 
    FROM   dbo.Company as C
    WHERE   C.HIERARCHY.IsDescendantOf(@hi) = 1 AND C.Id != @SupervisorId;
    RETURN;
End;

Any help is appreciated

CodePudding user response：

Split the commands

CREATE FUNCTION GetSubTree (@SupervisorID int)
RETURNS @Result TABLE(
    Id  INT     PRIMARY KEY NOT NULL,
    HIERARCHY   HIERARCHYID    NOT NULL ,
    FirstName   NVARCHAR(50)    NOT NULL,
    LastName   NVARCHAR(50)    NOT NULL,
    NationalCode    bigint    NOT NULL,
    EmployeeRole    NVARCHAR(3) NOT NULL
)
AS
BEGIN
    DECLARE @hi HIERARCHYID;
    DECLARE @hiT TABLE(
        HI HIERARCHYID
    )

    SELECT Top 1 @hi= HIERARCHY
    FROM dbo.Company as C1
    WHERE C1.Id = @SupervisorID;

    INSERT into @hiT
    VALUES
    (@hi)
    
    INSERT INTO @Result
    SELECT * 
    FROM   dbo.Company as C
    WHERE   C.HIERARCHY.IsDescendantOf(@hi) = 1 AND C.Id != @SupervisorId;
    RETURN;

End;