CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Arnthor
Arnthor

Reputation: 2623

Using while loop in T-SQL function

Non-database programmer here. It happens so, that I need to create a function in T-SQL which returns workdays count between given dates. I believe that the easiest how it's done is with while loop. Problem is, that as soon as I write something like

while @date < @endDate
begin

end

the statement won't execute, claiming "incorrect syntax near the keyword 'return'" (not very helpful). Where's the problem?

P.S. Full code:

ALTER FUNCTION [dbo].[GetNormalWorkdaysCount] (
@startDate DATETIME,
@endDate DATETIME
)   
RETURNS INT

AS
BEGIN
    declare @Count INT,
            @CurrDate DATETIME
    set @CurrDate = @startDate

    while (@CurrDate < @endDate)
    begin

    end

    return @Count
END
GO

Upvotes: 0

Views: 14307

Answers (2)

ulath
ulath

Reputation: 366

Using any loop within SQL server is never a good idea :)

There are few better solutions, referring to one presented on StackOverflow already.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Unlike some languages, the BEGIN/END pair in SQL Server cannot be empty - they must contain at least one statement.

As to your actual problem - you've said you're not a DB programmer. Most beginners to SQL tend to go down the same route - trying to write procedural code to solve the problem.

Whereas, SQL is a set-based language - it's usually better to find a set-based solution, rather than using loops.

In this instance, a calendar table would be a real help. Such a table contains one row for each date, and additional columns indicating useful information for your business (e.g. what you consider to be a working day). It then makes your query for working days look like:

SELECT COUNT(*) from Calendar
where BaseDate >= @StartDate and BaseDate < @EndDate and IsWorkingDay = 1

Populating the Calendar table becomes a one off exercise, and you can populate it with e.g. 30 years worth of dates easily.

Upvotes: 5

Related Questions