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
fnCalculateOutw orkerPaymentFor Box, Line 15
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Procedure
fnCalculateOutw orkerPaymentFor Box, Line 23
Line 23: Incorrect syntax near ')'.
-----
-----
CREATE FUNCTION fnCalculateOutw orkerPaymentFor Box(@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) + (NoOfDescriptio nBooks * 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) + (NoOfDescriptio nBooks * 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 ,
[NoOfDescription Books] [int] NOT NULL CONSTRAINT
[DF_OutworkerBox es_NoOfDescript ionBooks] DEFAULT (0),
CONSTRAINT [PK_OutworkerBox es] 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 fnCalculateOutw orkerPaymentFor Box(@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) + (NoOfDescriptio nBooks *
0.30)
FROM OutworkerBoxes
WHERE BoxID = @boxid
ELSE
SELECT @Result = ((endref - StartRef) * 0.25) + (NoOfDescriptio nBooks *
0.20)
FROM OutworkerBoxes
WHERE BoxID = @boxid
RETURN @Result

END
GO

ALTER FUNCTION fnCalculateOutw orkerPaymentFor Box(@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
) + (NoOfDescriptio nBooks *
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********@hot mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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
fnCalculateOutw orkerPaymentFor Box, Line 15
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Procedure
fnCalculateOutw orkerPaymentFor Box, Line 23
Line 23: Incorrect syntax near ')'.
-----
-----
CREATE FUNCTION fnCalculateOutw orkerPaymentFor Box(@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) + (NoOfDescriptio nBooks * 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) + (NoOfDescriptio nBooks * 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 ,
[NoOfDescription Books] [int] NOT NULL CONSTRAINT
[DF_OutworkerBox es_NoOfDescript ionBooks] DEFAULT (0),
CONSTRAINT [PK_OutworkerBox es] 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
) + (NoOfDescriptio nBooks *
CASE WHEN @BoxCount > 1 THEN 0.30 ELSE 0.20 END)
----

Many thanks for your prompt reply!