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
– Vikdor, Commented Oct 8, 2012 at 6:59

Damien_The_Unbeliever · Accepted Answer · 2012-10-08 07:12:25Z

5

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.

edited Oct 8, 2012 at 7:12

answered Oct 8, 2012 at 7:06

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:26:52Z

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, 2017 at 10:26

CommunityBot

11 silver badge

answered Oct 8, 2012 at 7:14

ulath

3663 silver badges11 bronze badges

2 Comments

t-clausen.dk Over a year ago

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: stackoverflow.com/questions/12776195/…

ulath Over a year ago

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

Collectives™ on Stack Overflow

Using while loop in T-SQL function

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related