SQL Server - Case construct in function

Asked By Fran on 21-Jul-08 07:35 AM
There is something wrong with this function.
I am struggling with syntax with the case construct.

CREATE FUNCTION GetPlannedDate
(
@Date_expedition datetime
@Time_expedition varchar(30)
)
RETURNS datetime
AS
BEGIN
DECLARE @ResultVar datetime
DECLARE @Hours int
DECLARE @Minutes int

set @Hours =
CASE @Time_expedition
when '<certain text>' then @Hours=17
ELSE
then left(@Time_expedition ,2)
END


@ResultVar= dateadd(hh, @Datum_Lossen, @Uren)
RETURN @ResultVar

END

I need to declare @Time_expedition is the error message.

Frans




Uri Dimant replied on 21-Jul-08 07:45 AM
Frans
See if this helps
CREATE FUNCTION GetPlannedDate

(

@Date_expedition datetime,

@Time_expedition varchar(30)

)

RETURNS datetime

AS

BEGIN

DECLARE @ResultVar datetime

DECLARE @Datum_Lossen datetime

DECLARE @Uren DATETIME

DECLARE @Hours int

DECLARE @Minutes int

SELECT @Hours= CASE @Time_expedition

when '<certain text>' then 17

ELSE left(@Time_expedition ,2)

END



SET @ResultVar= DATEDIFF(hh, @Datum_Lossen, @Uren)

RETURN @ResultVar

END
Roy Harvey (SQL Server MVP) replied on 21-Jul-08 09:24 AM
You have many syntax errors.  No comma between the two parameters of
the function.  The word then after the ELSE.  @Hours= embedded in the
CASE expression.  Referencing @variables without declaring them first.
Trying to assign a value without putting the SET keyword in front.

(You don't need to declare  @Time_expedition because it is declared as
a parameter.)

Roy Harvey
Beacon Falls, CT


On Mon, 21 Jul 2008 04:35:02 -0700, Frans
Gert-Jan Strik replied on 22-Jul-08 08:46 AM
In addition: I assume @Datum_Lossen is actually @Date_expedition, and of
type datetime. If the DATEADD first parameter is "hh" (indicating
hours), then the second parameter should be the number of hours to add,
and the third to datetime to add this to. Currently you have the 2nd and
3rd paramter switched.

--
Gert-Jan
SQL Server MVP