CODEWITHSUNDEEP
sqlsql-serverdatabasefunctiondate
Macukadam
Macukadam

Reputation: 45

Date Function Returning Null Values in SQL Server

Create FUNCTION [dbo].[NewFinishDate]
    (@HourlyQ decimal(18,2), @TotalQ decimal(18,2),
     @Monday integer, @Tuesday integer, @Wednesday integer,
     @Thursday integer, @Friday integer, @Saturday integer, @Sunday integer)
RETURNS date
AS
BEGIN
    DECLARE @DayName nvarchar(10), @DayHour integer,
            @Calculation Decimal(18,2), @Date as date,
            @i integer, @Finishdate date, @Addeddate date

    SELECT 
        @Date = DATEADD(DAY, -1, [Starting date]) 
    FROM
        [Quantities&Planning]

    WHILE @Calculation < @TotalQ 
    BEGIN
        SELECT @i += 1

        SELECT @Addeddate = DATEADD(DAY, @i, @date)
        SELECT @DayName = DATENAME(WEEKDAY, @Addeddate) 

        IF @DayName ='Monday' 
        BEGIN
            set @DayHour = @Monday
            set @Calculation += @HourlyQ*@DayHour
        end
        else if @DayName ='Tuesday' 
        begin
            set @DayHour = @Tuesday
            set @Calculation += @HourlyQ*@DayHour
        end

        if @DayName ='Wednesday' 
        begin
            set @DayHour = @Wednesday
            set @Calculation += @HourlyQ*@DayHour
        end
        else if @DayName ='Thursday' 
        begin
            set @DayHour = @Thursday
            set @Calculation += @HourlyQ*@DayHour
        end
        else if @DayName ='Friday' 
        begin
            set @DayHour = @Friday
            set @Calculation += @HourlyQ*@DayHour
        end
        else if @DayName ='Saturday' 
        begin
            set @DayHour = @Saturday
            set @Calculation += @HourlyQ*@DayHour
        end
        else if @DayName ='Sunday' 
        begin
            set @DayHour = @Sunday
            set @Calculation += @HourlyQ*@DayHour
        end         
    end     

    SELECT
        @Finishdate = DATEADD(DAY, @i, [Starting date]) 
    FROM
        [Quantities&Planning]

    RETURN @Finishdate
END

SELECT
    [dbo].[NewFinishDate](2,5000,2,2,2,2,2,2,2) as Yekeee 
FROM
    [Quantities&Planning]

This code is returning NULL values. I have a [Quantities&Planning] table and [StartingDate] column which is including date type variables. I have tried to return another variable like nvarchar and integer also. The problem maybe in the while loop.

Upvotes: 0

Views: 846

Answers (1)

TT.
TT.

Reputation: 16144

Did you realise that the following statement:

SELECT 
    @Date = DATEADD(DAY, -1, [Starting date]) 
FROM
    [Quantities&Planning]

Assigns the last [Starting date] (minus 1 day) as returned from the whole table [Quantities&Planning] to @Date? That last row is arbitrary even, as you have no ORDER BY clause in your select statement.

The same thing for assigning to @Finishdate... You probably want to assign from a specific row, yes? Then add a proper WHERE clause that selects only that specific row.

Second problem, you never initialize the @i variable, so it is NULL at the point you run:

SELECT @i += 1

@i will keep being NULL for the whole procedure (operations on NULL yield NULL).

You keep using this NULL value to add to dates, and that results in NULL values also.

Same problem with @Calculation... you need to initialize your variables before using them! It's not because you declare a variable that they are automatically initialized to 0 for integers and decimals. They are initialized as NULL unless you initialize them differently.

Upvotes: 2

Related Questions