Using Execute() method in Table-valued function SQL Server -

i trying create table-valued function in sql server. issue can't find right syntax sql. keep getting errors. don't know if possible use execute() method in table-valued function. have tried declaring , setting variables , using execute method in oridinary sql query , works.

my sql:

create function sortroutepartbyday (        @date datetime ) returns table  begin     declare @cmdtext varchar(max)       declare @daynameofweek varchar(10)     set @daynameofweek = datename(weekday, @date)     set @cmdtext =  'select * routepartpart ' +@daynameofweek+' =1';      return      (         execute(@cmdtext)     ) go 

my error far is:

msg 156, level 15, state 1, procedure sortroutepartbyday, line 21
incorrect syntax near keyword 'execute'.
msg 102, level 15, state 1, procedure sortroutepartbyday, line 23
incorrect syntax near ')'.

routepartpart ddl:

    create table [dbo].[routepartpart](         [routeid] [int] not null,         [routepartno] [smallint] not null,         [routepartpartno] [smallint] not null,         [pickupareagrpid] [int] null,         [deliveryareagrpid] [int] null,         [monday] [bit] null,         [tuesday] [bit] null,         [wednesday] [bit] null,         [thursday] [bit] null,         [friday] [bit] null,         [saturday] [bit] null,         [sunday] [bit] null,         [pickup] [bit] null,         [delivery] [bit] null,         [types] [varchar](10) null     ) on [primary] 

i think using dynamic sql unnecessary in case, try 1 -

query:

create function sortroutepartbyday (        @date datetime ) returns table  return      select *       dbo.routepartpart       datename(weekday, @date) = 1 

small info:

functions on sql server not same stored procedures, have several limitations on things can done. example, can't use dynamic sql.

update:

create function sortroutepartbyday (        @date datetime ) returns table  return      select p.*       dbo.routepartpart p      cross join (           select [weekday] = datename(weekday, @date)          ) t       ([weekday] = 'monday' , [monday] = 1)           or ([weekday] = 'tuesday' , [tuesday] = 1)           or ([weekday] = 'wednesday' , [wednesday] = 1)           or ([weekday] = 'thursday' , [thursday] = 1)           or ([weekday] = 'friday' , [friday] = 1)           or ([weekday] = 'saturday' , [saturday] = 1)           or ([weekday] = 'sunday' , [sunday] = 1)