Using while loop in T-SQL function

Question

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

Code snippet with function declaration and all the return statements would be helpful, to interpret the error message. — Vikdor, Oct 08 '12 at 06:59

Damien_The_Unbeliever · Accepted Answer · 2012-10-08T07:12:25.377

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.

score 3 · Answer 2 · edited May 23 '17 at 10:26

3

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

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

edited May 23 '17 at 10:26

Community

1
1

answered Oct 08 '12 at 07:14

ulath

366
3
11

2

using loop is never a good idea ? Never is a long time. I can give you several examples where looping is good. Normally i avoid looping, but in this example i think it is the best answer: http://stackoverflow.com/questions/12776195/how-to-change-case-in-string – t-clausen.dk Oct 08 '12 at 09:23
1

Ok, let me re-state this - It's not a good idea to start thinking about looping before trying other options. Example your're referring to is simply soluble without looping, see my answer in there ;) – ulath Oct 08 '12 at 09:43

Using while loop in T-SQL function

2 Answers2