CODEWITHSUNDEEP
sqlsql-serverfunctionwhile-loop
Mike - SMT
Mike - SMT

Reputation: 15236

SQL function appears to be stuck in loop

I am currently working on a function that should be returning 3 business days prior to the date sent as an argument to the function.

I am somewhat new to functions in SQL and I believe my logic is sound but I am stuck.

I have 2 tables I made. One contains all the weekend dates and one contains all the holiday dates. The function is meant to take in a single date and then using 2 while loops calculate 3 business days prior to the date submitted and then return that new date into my select statement.

Here is the function in quesion:

FUNCTION [dbo].[lessThreeBD](@WORKING_DT DATE)  
RETURNS DATE   
AS  
BEGIN
DECLARE @ThreeBD DATE
DECLARE @COUNTER INT
SET @COUNTER = 0

WHILE @COUNTER < 4
SET @ThreeBD = @WORKING_DT
BEGIN
    WHILE CASE WHEN (SELECT TOP 1 HOL.DATE from DATABASE.dbo.Holidays HOL where HOL.DATE = CAST(@WORKING_DT AS DATE)) IS NULL THEN 'NOT HOLIDAY' ELSE 'HOLIDAY' END = 'HOLIDAY'
    OR CASE WHEN (SELECT TOP 1 WKND.DATE from DATABASE.dbo.Weekends WKND where WKND.DATE = CAST(@WORKING_DT AS DATE)) IS NULL THEN 'WEEKDAY' ELSE 'WEEKEND' END = 'WEEKEND'
    BEGIN
        SET @WORKING_DT = DATEADD(DAY, -1, @WORKING_DT)
    END
    SET @WORKING_DT = DATEADD(DAY, -1, @WORKING_DT)
    SET @COUNTER  = @COUNTER + 1
SET @ThreeBD = @WORKING_DT
END
RETURN @ThreeBD
END

However when I test this with a single date it appears to be locked up:

Test line:

SELECT DATABASE.dbo.lessThreeBD('2020-05-24') AS TESTING

This line should return the 20th.

Any idea what I might be doing wrong here?

Keep in mind that both the holiday and weekend tables work perfectly fine. I have already tested the case statements and I get the expected results with each case statement.

Upvotes: 0

Views: 459

Answers (3)

Mike - SMT
Mike - SMT

Reputation: 15236

After fixing the null issue and the issue with SET not being inside of BEGIN I had to make a couple more changes to make this work accuratly.

The final function is as follows for anyone who would like it for their own use:

FUNCTION [dbo].[lessThreeBD](@WORKING_DT DATE)  
RETURNS DATE   
AS  
BEGIN
    DECLARE @COUNTER INT
    SET @COUNTER = 0

    WHILE @COUNTER < 3
    BEGIN
        SET @WORKING_DT = DATEADD(DAY, -1, @WORKING_DT)
        SET @COUNTER  = @COUNTER + 1
        WHILE (SELECT SQL_VARIANT_PROPERTY((SELECT TOP 1 HOL.DATE as CT from crmCAST.dbo.Holidays HOL where HOL.DATE = CAST(@WORKING_DT AS DATE)), 'BaseType')) IS NOT NULL
        OR (((DATEPART(DW, @WORKING_DT) - 1 ) + @@DATEFIRST ) % 7) IN (0,6)
        BEGIN
            SET @WORKING_DT = DATEADD(DAY, -1, @WORKING_DT)
        END
    END
RETURN @WORKING_DT
END

I was able to remove my weekend table with the use of (((DATEPART(DW, @WORKING_DT) - 1 ) + @@DATEFIRST ) % 7) IN (0,6) that will tell me if the date is a weekend so the only thing you will need is a table that has a list of holidays your business observes so you can have an accurate 3 business day return.

Upvotes: 0

Wouter
Wouter

Reputation: 2976

I think the issue is here:

WHILE @COUNTER < 4 SET @ThreeBD = @WORKING_DT BEGIN

this SET command is being executed in an infinite loop.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89444

This

WHILE @COUNTER < 4
SET @ThreeBD = @WORKING_DT

is equivilent to 

WHILE @COUNTER < 4
BEGIN
  SET @ThreeBD = @WORKING_DT
END

which is an infinite loop.

Always use BEGIN / END with control flow statements in TSQL.

WHILE @COUNTER < 4
BEGIN
  SET @ThreeBD = @WORKING_DT
  BEGIN
. . .

Except, perhaps, for common idioms like

if @@trancount > 0 rollback;

Upvotes: 2

Related Questions