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

Question

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

我的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

到目前为止我的错误是:

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 ')'.

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]

Answer 1

我认为在你的情况下没有必要使用动态 SQL，所以试试这个 -

查询:

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

小信息:

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

更新:

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)