CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
SBB
SBB

Reputation: 8970

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?

Upvotes: 0

Views: 106

Answers (3)

JamieD77
JamieD77

Reputation: 13949

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();

        RETURN CASE WHEN @startDate IS NOT NULL AND @completionDate IS NOT NULL THEN 'Complete'
                    WHEN @dueDate < @now THEN 'Past Due'
                    WHEN @startDate IS NOT NULL AND @completionDate IS NULL THEN 'In Progress'
                    WHEN @startDate IS NULL AND @completionDate IS NULL THEN 'Inactive'
                    END
    END

this should cover your logic

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

How about a completely different approach here? Scalar functions are horribly inefficient and not very flexible. Usually a better approach is to use an inline table valued function. This means you have to use a single select statement. Of course your whole series of nested IF statements could be compressed to a couple case expressions. This is far simpler to maintain, more flexible and will perform better.

ALTER FUNCTION [dbo].[Fetch_TaskStatus]
(
    @startDate DATE = NULL,
    @dueDate DATE = NULL,
    @completionDate DATE = NULL
)
RETURNS table as return
    select TaskStatus = 
        Case
            WHEN @startDate IS NULL AND @completionDate IS NULL then
                Case when @dueDate < GETUTCDATE() then 'Past Due' else 'Inactive' end

            WHEN @startDate IS NOT NULL AND @completionDate IS NULL then
                Case when @dueDate < GETUTCDATE() then 'Past Due' else 'In Progress' end
            WHEN @startDate IS NOT NULL AND @completionDate IS NOT NULL then 'Complete'
        END

Upvotes: 0

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

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;

Upvotes: 2

Related Questions