在表值函数SQL Server中使用Execute()方法

I am trying to create a table-valued function in SQL Server. My issue is that I can't find the right syntax for the SQL. I keep getting errors. I don't know if it is possible to use an execute() method in a table-valued function. I have tried declaring and setting the variables and also using the execute method in an oridinary sql query and it works.

我正在尝试在SQL Server中创建表值函数。我的问题是，我无法找到SQL的正确语法。我越来越错误。我不知道是否可以在表值函数中使用execute()方法。我尝试过声明和设置变量，并在一个特定的sql查询中使用execute方法，它是有效的。

My SQL:

我的SQL:

CREATE FUNCTION SortRoutePartByDay
(   
    @date datetime
)
RETURNS TABLE 
AS
Begin
    declare @cmdtext varchar(max)  
    declare @Daynameofweek varchar(10)
    set @Daynameofweek = datename(weekday, @date)
    set @cmdtext =  'select * from RoutePartPart where ' +@Daynameofweek+' =1';

    RETURN 
    (
        execute(@cmdtext)
    )
GO

My error so far is:

我到目前为止的错误是:

Msg 156, Level 15, State 1, Procedure SortRoutePartByDay, Line 21
Incorrect syntax near the keyword 'execute'.
Msg 102, Level 15, State 1, Procedure SortRoutePartByDay, Line 23
Incorrect syntax near ')'.

Msg 156, 15级，状态1，过程SortRoutePartByDay，第21行，关键字“执行”附近的语法错误。Msg 102，第15级，状态1，过程SortRoutePartByDay，第23行错误的语法靠近')。

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]

1 个解决方案

#1

I think using dynamic SQL is unnecessary in your case, so try this one -

我认为使用动态SQL在你的例子中是不必要的，所以试试这个-

Query:

查询:

CREATE FUNCTION SortRoutePartByDay
(   
    @date DATETIME
)
RETURNS TABLE 
AS RETURN
     SELECT * 
     FROM dbo.RoutePartPart 
     WHERE DATENAME(weekday, @date) = 1

Small info:

小的信息:

Functions on SQL Server are not the same as stored procedures, they have several limitations on the things that can be done. For example, you can't use dynamic SQL.

SQL Server上的函数与存储过程不同，它们对可以完成的事情有一些限制。例如，您不能使用动态SQL。

Update:

更新:

CREATE FUNCTION SortRoutePartByDay
(   
    @date DATETIME
)
RETURNS TABLE 
AS RETURN
     SELECT p.* 
     FROM dbo.RoutePartPart p
     CROSS JOIN (
          SELECT [WeekDay] = DATENAME(weekday, @date)    
     ) t 
     WHERE ([WeekDay] = 'Monday' AND [Monday] = 1)
          OR ([WeekDay] = 'Tuesday' AND [Tuesday] = 1)
          OR ([WeekDay] = 'Wednesday' AND [Wednesday] = 1)
          OR ([WeekDay] = 'Thursday' AND [Thursday] = 1)
          OR ([WeekDay] = 'Friday' AND [Friday] = 1)
          OR ([WeekDay] = 'Saturday' AND [Saturday] = 1)
          OR ([WeekDay] = 'Sunday' AND [Sunday] = 1)

#1