Use of parameters in Multi-statement function SQL Server

Question

I'm having trouble creating a simple function for a car rental database in SQL Server.

CREATE FUNCTION F_late_price 
    (@from_date DATETIME, 
     @to_date DATETIME,
     @fee_per_day INT) 
RETURNS @Late_Customers_fees TABLE                  
                    (Id_Cust INT, 
                     Date_of_Rent_End_Due DATETIME,
                     Date_of_Rent_End DATETIME,
                     Total_fee AS (DATEDIFF(DAY, Date_of_Rent_End_Due, Date_of_Rent_End)) * @fee_per_day
                    )                                                                  
AS                                                                  
BEGIN 
    IF @from_date <= @to_date 
    BEGIN
        INSERT INTO @Late_Customers_fees
            SELECT 
                Id_Cust, Date_of_Rent_End_Due, Date_of_Rent_End
            FROM 
                F_late_customers(@from_date, @to_date)
    END

    RETURN                                                         
END

The F_late_customers function is passed two dates and returns a table with customers whose end date is past their due date:

CREATE FUNCTION F_late_customers 
    (@from_date DATETIME,
     @to_date DATETIME)
RETURNS @Late_Customers_details TABLE                  
                        (Id_Cust INT,
                         F_Name NVARCHAR(16),
                         L_Name NVARCHAR(16),
                         Date_of_Rent_Start DATETIME,
                         Date_of_Rent_End_Due DATETIME,
                         Date_of_Rent_End DATETIME
                        )                    
AS 
BEGIN                                     
    IF @from_date <= @to_date 
    BEGIN
        INSERT INTO @Late_Customers_details
            SELECT  
                dbo.Customers.Id_Cust,
                dbo.Customers.F_Name, dbo.Customers.L_Name, 
                dbo.Rents.Date_Of_Rent_Start, 
                dbo.Rents.Date_Of_Rent_End_Due, dbo.Rents.Date_Of_Rent_End
            FROM    
                dbo.Customers 
            INNER JOIN 
                dbo.Rents ON dbo.Customers.Id_Cust = dbo.Rents.ID_Cust
            WHERE  
                (dbo.Rents.Date_Of_Rent_End_Due) < (dbo.Rents.Date_Of_Rent_End)
    END

    RETURN
END

But the F_late_price function throws an error about using @fee_per_day as a parameter in the create table statement.

Is there any way to solve this problem?

Any help would be appreciated

Thank you

marc_s · Accepted Answer · 2018-05-04 19:37:46Z

You can't use this in a table declaration:

Total_fee AS (DATEDIFF(DAY, Date_of_Rent_End_Due, Date_of_Rent_End)) * @fee_per_day

So something like this:

CREATE FUNCTION  F_late_price 
    (@from_date DATETIME, 
     @to_date DATETIME,
     @fee_per_day INT) 
RETURNS @Late_Customers_fees TABLE                  
                        (Id_Cust INT, 
                         Date_of_Rent_End_Due DATETIME,
                         Date_of_Rent_End DATETIME,
                         Total_fee AS DECIMAL(18,2)   
                        )                                                                  
AS                                                                  
BEGIN 
    IF @from_date <= @to_date 
    BEGIN
        INSERT INTO @Late_Customers_fees
            SELECT 
                Id_Cust, Date_of_Rent_End_Due, Date_of_Rent_End, 
                (DATEDIFF(DAY, Date_of_Rent_End_Due, Date_of_Rent_End)) * @fee_per_day AS TotalFee
            FROM 
                F_late_customers(@from_date, @to_date)
    END

    RETURN                                                         
END

The TotalFee data type may not be what you need so you may need to change that.

Collectives™ on Stack Overflow

Use of parameters in Multi-statement function SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related