Dynamic Sql In Function Issue

i want to know if i can use dynamic sql in a function like this:



ALTER FUNCTION [dbo].[udf_GetGradeAvgByNewHireID]
@CourseIDs VARCHAR(MAX),
@NewHireID INT
)
RETURNS INT
AS
BEGIN

/*
1. The CourseIDs passed to this function will be in the form ",1,2,3,"
2. Replace the first and last comma with () so we will have "(1,2,3)"
3. Use this with the IN clause to filter out the right classes for these IDs
*/

DECLARE @SQL VARCHAR(MAX);

--grade average
DECLARE @GradeAvg INT;

--formatting the string to be in the form (1,2,3) from ,1,2,3,4,
SET @CourseIDs = '|' + @CourseIDs + '|';
SET @CourseIDs = REPLACE(@CourseIDs,'|,','(');
SET @CourseIDs = REPLACE(@CourseIDs,',|',')');

SET @SQL = 'SELECT @GradeAvg = AVG(NHC.Class_Grade)
FROM
New_Hires_Classes NHC
INNER JOIN Classes Cls ON NHC.Class_ID=Cls.Class_ID
WHERE Cls.Course_ID IN ' + @CourseIDs +
' AND NHC.New_Hire_ID=@NewHireID';
EXECUTE @SQL

RETURN @GradeAvg


becase when i try to call this function from the query i get this error:


The name 'SELECT @GradeAvg = AVG(NHC.Class_Grade)
FROM
New_Hires_Classes NHC
INNER JOIN Classes Cls ON NHC.Class_ID=Cls.Class_ID
WHERE Cls.Course_ID IN (1,2,3,4,5,6) AND NHC.New_Hire_ID=@NewHireID' is not a valid identifier.