return more than 1 value in @ table variable

ALTER FUNCTION [dbo].[fn_DivisonCode] (@PeopleID int)
RETURNS @temptable TABLE (Code varchar(100), ID varchar(100))
AS
begin

DECLARE @stDeptCode VARCHAR(100)
DECLARE @peopleID VARCHAR(100)

SELECT 
      @stDeptCode = (COALESCE(@stDeptCode + ',', '') + CAST(PPL.DeptCode AS VARCHAR(5))),
      @peopleID = peopleID
FROM dbo.PPL
WHERE PeopleID = peopleID 

INSERT INTO @temptable(Code, ID) 
  VALUES(@stDeptCode, @peop开发者_运维问答leID)

return
end

SELECT [dbo].[fn_DivisonCode](23415)

When I call the function it should return 2 values but here it returns only the @stDeptCod values not the @peopleID. Is there anything that I am missing here?

Thanks

You are selecting values into variables. A variable can only hold a single value at a time, so when you run your select statement, it may select one, or two, or 100 values from the table, but only the last one is stored in the variable after the statement completes. Instead, combine you select and insert statements, like this:

insert into @temptable(Code,ID)
  SELECT (COALESCE(@stDeptCode + ',', '')  +  CAST(PPL.DeptCode AS VARCHAR(5))), peopleID
    FROM dbo.PPL  
    WHERE @PeopleID = peopleID