SQL Server - Help converting proc to a function

Need help converting the below query to a function

ALTER PROCEDURE [dbo].[rpt_outboundagentstatus]

AS
BEGIN

    SET NOCOUNT ON;

declare @query nvarchar(4000)
declare @agentstats nvarchar (4000)

select @agentstats=  STUFF((
    SELECT DISTINCT
      '],[' + ltrim((StatusKey))
    FROM CIC_AgentActivityLog
    ORDER BY '],[' + ltrim((StatusKey))
    FOR XML PATH('')), 1, 2, '') + ']'

set @query =
'select *
from (
SELECT   CAST(a.StatusDateTime AS DAte) AS Date, er.EmployeeID, a.UserId, c.CampaignName, a.StatusKey,a.StateDuration
FROM     dbo.CIC_AgentActivityLog AS a INNER JOIN
            dbo.IndivDetails_vel1 AS r ON a.UserId = r.ICUserID AND DATEDIFF(m, a.StatusDateTime, GETDATE()) <= 1 AND r.Active = 1 INNER JOIN
            dbo.hr_EmployeeRollCall AS er ON r.StreetAddress = er.EmployeeID COLLATE Latin1_General_CI_AS AND CAST(a.StatusDateTime AS DAte)
            = er.RollCallDate INNER JOIN
            dbo.hr_Campaign AS c ON er.CampaignID = c.CampaignID AND c.IsActiveReports = 1 ) as agentstats
            PIVOT (sum(StateDuration) for StatusKey in ('+@agentstats+')) as pvt'

 EXECUTE (@query)  
END

Why would you want something like this to be a function?

Need to call this in a view..and functions are the only way that i could think of calling the results in a view.
The results from this query needs to be joined to another query therefore the need....

You'd just use CREATE FUNCTION instead of CREATE PROCEDURE and have it use a return type of TABLE.

That said, your tactics here will crush the performance of your view.  This is going to perform horribly no matter what you do because this code will get executed for every row in your view.  Not good...

One thing you could do to improve it a bit is to use a TABLE variable instead of that FOR XML stuff you've got in the first dynamic sql statement (dynamic sql is bad anyway because it will impact SQL Server's ability to create and cache an execution plan).  This url will show you how to work with them:

https://www.nullskull.com/a/705/sql-server-table-variables-to-eliminate-cursors.aspx

Then, adjust your query below to select from the table variable versus your dynamic string of status types.  That should let you eliminate all this dynamic sql and return the FUNCTION value as a "table" data type.