Hi all,

I have the below user-defined function on mssql 2000 and I can't work
out why i'm getting the following error:
-----
Server: Msg 156, Level 15, State 1, Procedure
fnCalculateOutworkerPaymentForBox, Line 15
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Procedure
fnCalculateOutworkerPaymentForBox, Line 23
Line 23: Incorrect syntax near ')'.
-----
-----
CREATE FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)
RETURNS money

AS
BEGIN
RETURN (
/* if the box is a paperback */
IF (SELECT COUNT(BoxID) AS NoOfBoxes FROM OutworkerBoxes WHERE BoxID
= @boxid AND BoxCode LIKE '%PAPER%') > 1

/* If the books are paperback, charge 15p each and add on 30p for a
description book to make 45p */
SELECT ((endref - StartRef) * 0.15) + (NoOfDescriptionBooks * 0.30)
FROM OutworkerBoxes WHERE BoxID = @boxid
ELSE
/* If the books are normal, charge 25p each and add 20p on for
description books to make 45p */
SELECT ((endref - StartRef) * 0.25) + (NoOfDescriptionBooks * 0.20)
FROM OutworkerBoxes WHERE BoxID = @boxid

)

END
-----

Below is the sql for the table it works with:

-----
CREATE TABLE [OutworkerBoxes] (
[BoxID] [int] IDENTITY (1, 1) NOT NULL ,
[OutworkerID] [int] NOT NULL ,
[ImportedBy] [int] NULL ,
[StartRef] [int] NOT NULL ,
[endref] [int] NOT NULL ,
[DateIssued] [datetime] NOT NULL ,
[BoxCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[DealerID] [int] NULL ,
[StatusID] [int] NOT NULL ,
[IssuedBy] [int] NOT NULL ,
[BoxNotes] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[DateImported] [datetime] NULL ,
[NoOfDescriptionBooks] [int] NOT NULL CONSTRAINT
[DF_OutworkerBoxes_NoOfDescriptionBooks] DEFAULT (0),
CONSTRAINT [PK_OutworkerBoxes] PRIMARY KEY CLUSTERED
(
[BoxID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

-----

If anyone can advise me i'd be most grateful.

Thanx in advance

James

IF is a control-of-flow statement so you can't specify it as a RETURN
expression. Below are a couple of untested examples that show how you can
return the desired expression.

CREATE FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)
RETURNS money
AS
BEGIN
DECLARE @BoxCount int
DECLARE @Result money
SELECT @BoxCount = COUNT(BoxID)
FROM OutworkerBoxes
WHERE BoxID = @boxid AND BoxCode LIKE '%PAPER%'

IF @BoxCount > 1
SELECT @Result = ((endref - StartRef) * 0.15) + (NoOfDescriptionBooks *
0.30)
FROM OutworkerBoxes
WHERE BoxID = @boxid
ELSE
SELECT @Result = ((endref - StartRef) * 0.25) + (NoOfDescriptionBooks *
0.20)
FROM OutworkerBoxes
WHERE BoxID = @boxid
RETURN @Result

END
GO

ALTER FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)
RETURNS money
AS
BEGIN
DECLARE @BoxCount int
SELECT @BoxCount = COUNT(BoxID)
FROM OutworkerBoxes
WHERE BoxID = @boxid AND BoxCode LIKE '%PAPER%'

RETURN(
SELECT ((endref - StartRef) *
CASE WHEN @BoxCount > 1 THEN 0.15 ELSE 0.25 END
) + (NoOfDescriptionBooks *
CASE WHEN @BoxCount > 1 THEN 0.30 ELSE 0.20 END)
FROM OutworkerBoxes
WHERE BoxID = @boxid
)
END
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

"phaser2001" <phaser2001@hotmail.com> wrote in message
news:1110636610.476826.178300@z14g2000cwz.googlegr oups.com...
> Hi all,
>
> I have the below user-defined function on mssql 2000 and I can't work
> out why i'm getting the following error:
> -----
> Server: Msg 156, Level 15, State 1, Procedure
> fnCalculateOutworkerPaymentForBox, Line 15
> Incorrect syntax near the keyword 'IF'.
> Server: Msg 170, Level 15, State 1, Procedure
> fnCalculateOutworkerPaymentForBox, Line 23
> Line 23: Incorrect syntax near ')'.
> -----
> -----
> CREATE FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)
> RETURNS money
>
> AS
> BEGIN
> RETURN (
> /* if the box is a paperback */
> IF (SELECT COUNT(BoxID) AS NoOfBoxes FROM OutworkerBoxes WHERE BoxID
> = @boxid AND BoxCode LIKE '%PAPER%') > 1
>
> /* If the books are paperback, charge 15p each and add on 30p for a
> description book to make 45p */
> SELECT ((endref - StartRef) * 0.15) + (NoOfDescriptionBooks * 0.30)
> FROM OutworkerBoxes WHERE BoxID = @boxid
> ELSE
> /* If the books are normal, charge 25p each and add 20p on for
> description books to make 45p */
> SELECT ((endref - StartRef) * 0.25) + (NoOfDescriptionBooks * 0.20)
> FROM OutworkerBoxes WHERE BoxID = @boxid
>
> )
>
> END
> -----
>
> Below is the sql for the table it works with:
>
> -----
> CREATE TABLE [OutworkerBoxes] (
> [BoxID] [int] IDENTITY (1, 1) NOT NULL ,
> [OutworkerID] [int] NOT NULL ,
> [ImportedBy] [int] NULL ,
> [StartRef] [int] NOT NULL ,
> [endref] [int] NOT NULL ,
> [DateIssued] [datetime] NOT NULL ,
> [BoxCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [DealerID] [int] NULL ,
> [StatusID] [int] NOT NULL ,
> [IssuedBy] [int] NOT NULL ,
> [BoxNotes] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [DateImported] [datetime] NULL ,
> [NoOfDescriptionBooks] [int] NOT NULL CONSTRAINT
> [DF_OutworkerBoxes_NoOfDescriptionBooks] DEFAULT (0),
> CONSTRAINT [PK_OutworkerBoxes] PRIMARY KEY CLUSTERED
> (
> [BoxID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> -----
>
> If anyone can advise me i'd be most grateful.
>
> Thanx in advance
>
> James
>

Thanx - i just tried the second suggestion and it works a treat :-) The
only thing was that in the below section the boxcount had to be more
than 0, i.e. boxes existed, instead of one.

----
CASE WHEN @BoxCount > 1 THEN 0.15 ELSE 0.25 END
) + (NoOfDescriptionBooks *
CASE WHEN @BoxCount > 1 THEN 0.30 ELSE 0.20 END)
----

Many thanks for your prompt reply!