SBB SBB - 5 months ago 16
SQL Question

TSQL Scalar Function

I created a small function that takes a few date params and outputs a project status based on some logic I defined.

I am trying to figure out how I can "Break Out" of the function once a status has been set though. In my logic below, it seems that its always going to check the

dueDate
and set its status and then overwrite it self with the following check.

ALTER FUNCTION [dbo].[Fetch_TaskStatus]
(
-- Add the parameters for the function here
@startDate DATE = NULL,
@dueDate DATE = NULL,
@completionDate DATE = NULL
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @status varchar(100);

-- Declare our current date
DECLARE @now DATE = GETUTCDATE();

-- Logic
-- If our start date and completion date are missing..
IF(@startDate IS NULL AND @completionDate IS NULL)
BEGIN

-- If our due date is past the current date, its past due
IF(@dueDate < @now)
BEGIN
SET @status = 'Past Due';
END

-- We have a start date but the task has not been started.
SET @status = 'Inactive';

END

-- If we have a start date and no completion date
IF(@startDate IS NOT NULL AND @completionDate IS NULL)
BEGIN

-- Are we past due?
IF(@dueDate < @now)
BEGIN
SET @status = 'Past Due'
END

-- We are not past due, must be in progress
SET @status = 'In Progress'

END

-- If we have a start date and a completion date
IF(@startDate IS NOT NULL AND @completionDate IS NOT NULL)

BEGIN
-- We have started and completed our task
SET @status = 'Complete'
END

-- Return the result of the function
RETURN @status

END


Once a status has been set, I need to break out of this function so the status is not overwritten again by the logic that follows it.

Is there a better way to handle this?

Answer

Rewrite it as below:

ALTER FUNCTION [dbo].[Fetch_TaskStatus]
(
-- Add the parameters for the function here
@startDate DATE = NULL,
@dueDate DATE = NULL,
@completionDate DATE = NULL
)
RETURNS varchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @status varchar(100);

-- Declare our current date
DECLARE @now DATE = GETUTCDATE();

-- Logic
-- If our start date and completion date are missing..
IF(@startDate IS NULL AND @completionDate IS NULL)
    BEGIN

        -- If our due date is past the current date, its past due
        IF(@dueDate < @now)
        BEGIN
            RETURN  'Past Due';

        END

        -- We have a start date but the task has not been started.
            RETURN  'Inactive';

    END

    -- If we have a start date and no completion date
IF(@startDate IS NOT NULL AND @completionDate IS NULL)
    BEGIN

        -- Are we past due?
        IF(@dueDate < @now)
            BEGIN
            RETURN  'Past Due'

            END

        -- We are not past due, must be in progress
            RETURN  'In Progress'

    END

-- If we have a start date and a completion date
IF(@startDate IS NOT NULL AND @completionDate IS NOT NULL)

    BEGIN
        -- We have started and completed our task
            RETURN  'Complete'
    END

-- Return the result of the function
RETURN 'Unknown';

END

You can also make use of IF-ELSE block. I have rewrite it using IF-ELSE too, just check if your logic is guaranteed yet or not:

    CREATE FUNCTION [dbo].[Fetch_TaskStatus]
    (
      -- Add the parameters for the function here
      @startDate DATE = NULL ,
      @dueDate DATE = NULL ,
      @completionDate DATE = NULL
    )
 RETURNS VARCHAR(100)
AS
    BEGIN

-- Declare our current date
        DECLARE @now DATE = GETUTCDATE();

-- Logic
-- If our start date and completion date are missing..
        IF ( @startDate IS NULL
             AND @completionDate IS NULL
           )
            BEGIN
         -- If our due date is past the current date, its past due
                IF ( @dueDate < @now )
                    RETURN  'Past Due';
                ELSE
                        -- We have a start date but the task has not been started.
                    RETURN  'Inactive';
            END;

    -- If we have a start date and no completion date
        IF ( @startDate IS NOT NULL
             AND @completionDate IS NULL
           )
            BEGIN
            -- Are we past due?
                IF ( @dueDate < @now )
                    RETURN  'Past Due';
                ELSE
                        -- We are not past due, must be in progress
                    RETURN  'In Progress';
            END;

-- If we have a start date and a completion date
        IF ( @startDate IS NOT NULL
             AND @completionDate IS NOT NULL
           )
            -- We have started and completed our task
            RETURN  'Complete';

      -- Return the result of the function
        RETURN 'Unknown';
    END;