Dynamic SQL In Retrun Table Procedures

I'm having problems with dynamic sql within a funtion that returns a table. Here's an example, it compiles but on running gives me an error allong the lines that calling sub procedures is not permitted. I'm assuming the problem is inserting into teh return table, can it be done? should I declare @MyTable as a parameter using the sp_executesql call? or have I completely missed something?

Regards,

Andrew


Example routine, not that far from what my actual code looks like.

CREATE FUNCTION GetMyRecords
(
@date DateTime
)
RETURNS
@MyTable TABLE
(
RECORDID BigInt
)
AS
BEGIN
DECLARE @SQL Varchar(1000)
DECLARE @dayOfWeek int
DECLARE @fld varchar(10)
SET @dayOfWeek = DatePart( dw, @date )

if( @dayOfWeek = 1 )
@fld = "SUNDAY"
if( @dayOfWeek = 2 )
@fld = "MONDAY"
etc...

Set @SQL = N'Insert into @MyTable Select MyTestTable.ID from MyTestTable Where MyTestTable.' + @fld + ' > 0 '
Exec( @SQL )
RETURN
END
GO