In the script below is the DDL to create some tables and a UDF.
What I'm interested in is the UDF at the end. Specifically, these few
lines:
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0
If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
message:
"Incorrect syntax near keyword ELSE"
Being a good little footsoldier, I want to release resources
explicitly, but clearly I'm putting the CLOSE and DEALLOCATE statements
in the wrong place.
Could someone please tell me where I ought to put them so that the
cursor is CLOSEd and DEALLOCATEd correctly.
By the way, I am not after negative comments on the data design, or the
logic (or lack of it) in the function, just why the syntax error
occurs.
Thanks as ever
Edward
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Employee]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[PurchaseOrder]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TimesheetItem]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[WorkOTRate]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[WorkOTRateDefaults]
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PurchaseOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[Complete] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NOT NULL ,
[TimeTo] [datetime] NOT NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
Function to determine the actual cost, in minutes, of a particular
segment of work. This is what it does, or is supposed to do.
1. From the PARAMETER WorkID, determine the conclusion of the work
block associated with the TimesheetID - i.e. StartTime + DurationMins
2. Establish whether there are records in the WorkOTRate table
corresponding to this particular WorkID, weekday and time period
3. If there are, get the amount of minutes by which the work block
coincides.
4. If there are no such records, get the default values from the
WorkOTRateDefaults table
5. If the block doesn't cross any boundaries then it's just regular
work, so just count the minutes.
25/08/2005 EC
*/
CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@.TimesheetID int)
RETURNS float
AS
BEGIN
DECLARE
@.OTRateTimeFrom datetime,
@.OTRateTimeTo as datetime,
@.OTRateMultiplier as float,
@.EndTime datetime,
@.ReturnValue as float,
@.OrderRatePerHour as money,
@.EmployeeRatePerHour as smallmoney,
@.NumRecords as int,
@.WorkID as int,
@.EmployeeID as int,
@.StartTime as smalldatetime,
@.Duration as int,
@.Found as int,
@.Chargeable as bit
-- Get the various bits and bobs needed for the calculation
SET @.ReturnValue = 0
SET @.Found = 0
SELECT
@.WorkID = WorkID,
@.EmployeeID = EmployeeID,
@.StartTime = Start,
@.Duration = DurationMins
FROM
TimesheetItem
WHERE
ItemID = @.TimesheetID
-- If this work is NOT chargeable, return 0
SELECT
@.Chargeable = Chargeable
FROM
[Work]
WHERE
WorkID = @.WorkID
IF @.Chargeable = 1
BEGIN
SET @.EndTime = DATEADD(mi, @.Duration, @.StartTime)
-- Get the rate per hour for this work
SELECT
@.OrderRatePerHour = RatePerHour
FROM
PurchaseOrder
WHERE
WorkID = @.WorkID
-- Get the rate per hour for the employee
SELECT
@.EmployeeRatePerHour = DefaultRatePerHour
FROM
Employee
WHERE
(EmployeeID = @.EmployeeID)
-- Find out if there's an OT Rate set up for this WorkID
SELECT
@.NumRecords = Count(*)
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dd, @.StartTime)))
IF @.NumRecords > 0
BEGIN
DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dw, @.StartTime)))
OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
WHILE (@.@.fetch_status=0)
BEGIN
-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)
-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)
IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT
NUBMER 2
S E
OT OT
NUMBER 3
S E
OT OT
NUBMER 4
S E
OT OT
*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0
BEGIN
DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRateDefaults
WHERE
(WorkDay = DATEPART(dw, @.StartTime))
OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
WHILE (@.@.fetch_status=0)
BEGIN
-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)
-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)
IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)
/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT
NUBMER 2
S E
OT OT
NUMBER 3
S E
OT OT
NUBMER 4
S E
OT OT
*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
CLOSE OTRate
DEALLOCATE OTRate
-- If there were no matching OT records, it's just a regular block
of work in normal hours
IF @.Found = 0
SET @.ReturnValue = @.Duration
END
-- Finally we factor in the relation between the Employee's rate and
the Order's stated rate.
RETURN (@.ReturnValue * (@.EmployeeRatePerHour / @.OrderRatePerHour))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOteddysnips@.hotmail.com wrote:
> In the script below is the DDL to create some tables and a UDF.
> What I'm interested in is the UDF at the end. Specifically, these few
> lines:
> --CLOSE OTRate
> --DEALLOCATE OTRate
> ELSE-- @.NumRecords <= 0
I haven't actually read the code through thoroughly, so I don't know if
others are going to give you advice about doing it in a set oriented
fashion, but I believe that your close and deallocate are coming one
END too late. The two ENDs above them (to my reading) are the END of
the while loop and then the end of the if statement. When using ELSE,
the following should be adhered to:
IF <condition>
<statement or block>
ELSE
<statement or block
where statement is either a single statement or:
BEGIN
<statement> [<statement>...]
END
Damien|||Damien wrote:
> teddysnips@.hotmail.com wrote:
> > In the script below is the DDL to create some tables and a UDF.
> > What I'm interested in is the UDF at the end. Specifically, these few
> > lines:
> > --CLOSE OTRate
> > --DEALLOCATE OTRate
> > ELSE-- @.NumRecords <= 0
> I haven't actually read the code through thoroughly, so I don't know if
> others are going to give you advice about doing it in a set oriented
> fashion, but I believe that your close and deallocate are coming one
> END too late.
You're quite right - many thanks! As for doing it using sets - well, I
really don't have time!
Edward|||(teddysnips@.hotmail.com) writes:
> You're quite right - many thanks! As for doing it using sets - well, I
> really don't have time!
But you assume that anyone will have the time to run that code? I hope
that you can find the time to test it on full-size data, before you
devote your important time to something else!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
