Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Having problems with UDF that uses If statements and while loop

Posted on 2004-07-30
Medium Priority
795 Views
I just need some guru to fix my syntax. I spent over an hour trying to find the problem, but it just isn't obvious to me.

---------------------------------
CREATE   FUNCTION dbo.DaysOff
(

@dtStartDate smallDatetime,
@dtEndDate smalldatetime,
@strStartTime varchar(8),
@strEndTime varchar(8)

)
RETURNS decimal
AS
begin
DECLARE @dtStartTime DateTime,
@dtEndTime DateTime,
@sgHours decimal

If DateDiff(day,@dtStartDate , @dtEndDate) > 1
While @dtStartDate >= @dtEndDate
Begin
If DatePart(Weekday,@dtStartDate) > 1 and DatePart(Weekday,@dtStartDate) < 7
Set @sgHours = @sghours + 8
end
Else
If Isdate(@strStartTime)

Set @dtStartTime = Convert(datetime,@strStarttime,108)
If IsDate(@strEndTime)

Set @dtEndTime = Convert(DateTime,@strEndTime,108)
Set @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60

Else
Set @sgHours = 8

Else
Set @sgHours = 8

return @sgHours

end
0
Question by:Mach1pro
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 4

Accepted Solution

BulZeyE earned 1200 total points
ID: 11681104
If you have more than one statement inside of an if statement you have to use BEGIN and END.  I also added an ' = 1 ' to your checks for Isdate..  Try the following:

CREATE   FUNCTION dbo.DaysOff
(

@dtStartDate smallDatetime,
@dtEndDate smalldatetime,
@strStartTime varchar(8),
@strEndTime varchar(8)

)
RETURNS decimal
AS
begin
DECLARE @dtStartTime DateTime,
@dtEndTime DateTime,
@sgHours decimal

If DateDiff(day,@dtStartDate , @dtEndDate) > 1
BEGIN
While @dtStartDate >= @dtEndDate
Begin
If DatePart(Weekday,@dtStartDate) > 1 and DatePart(Weekday,@dtStartDate) < 7
Set @sgHours = @sghours + 8
end
END
Else

If Isdate(@strStartTime) = 1
BEGIN

Set @dtStartTime = Convert(datetime,@strStarttime,108)
If IsDate(@strEndTime) = 1
BEGIN

Set @dtEndTime = Convert(DateTime,@strEndTime,108)
Set @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
END
Else
BEGIN
Set @sgHours = 8
END
END
Else
BEGIN
Set @sgHours = 8
END

return @sgHours

end

hth.
0

LVL 75

Expert Comment

ID: 11681127
1 03/30/2004 500 how to use temptable as row source for c...  Open Microsoft Access
2 04/30/2004 250 Subform loses filter in adp when enterin...  Open Microsoft Access
3 05/05/2004 250 How to retain linked value in field when...  Open Microsoft Access
4 07/30/2004 300 Having problems with UDF that uses If st...  Open Microsoft SQL Server
5 07/15/2004 250 Bug crawling script  Open JavaScript

Thanks.
0

LVL 4

Expert Comment

ID: 11681225
after looking at your logic, I think this would return the desired results:

CREATE FUNCTION dbo.DaysOff
(
@dtStartDate  SMALLDATETIME,
@dtEndDate    SMALLDATETIME,
@strStartTime VARCHAR(8),
@strEndTime   VARCHAR(8)
)
RETURNS DECIMAL
AS
BEGIN
DECLARE @dtStartTime DATETIME,
@dtEndTime   DATETIME,
@sgHours     DECIMAL

IF DATEDIFF(DAY,@dtStartDate , @dtEndDate) > 1
BEGIN
WHILE @dtStartDate <= @dtEndDate
BEGIN
IF DATEPART(WEEKDAY,@dtStartDate) > 1 AND DATEPART(WEEKDAY,@dtStartDate) < 7
SET @sgHours = @sghours + 8

END
END
ELSE
IF ISDATE(@strStartTime) = 1 AND ISDATE(@strEndTime) = 1
BEGIN
SET @dtStartTime = CONVERT(DATETIME,@strStarttime,108)
SET @dtEndTime = CONVERT(DATETIME,@strEndTime,108)
SET @sgHours = dbo.MinutesWorked(@dtstartDate, @dtStartTime, @dtEndDate, @dtEndTime) /60
END
ELSE
BEGIN
SET @sgHours = 8
END

RETURN @sgHours

END
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month11 days, 6 hours left to enroll