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

Question

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.

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

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]

Devart · Accepted Answer · 2013-07-18 06:18:58Z

4

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

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.

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)

edited Jul 18, 2013 at 6:18

answered Jul 18, 2013 at 5:44

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adriaan Stander Over a year ago

+1 Correct, but maybe try to clarrify what went wrong. The difference between multi/single statement table functions? and Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Lahib Over a year ago

The @Daynameofweek parameter is a column in the table. This approach will compare the nvarchar value "Thursday" with the int 1. the weekday needs to be a column because i need to get all rows with the value 1.

Devart Over a year ago

@Lahib, OK. Can you provide DDL of your table.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related