IF..ELSE in function - unknown error

[phaser2001]

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

[Dan Guzman]

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" <ph********@hotmail.com> wrote in message
news:11**********************@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

[phaser2001]

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!