Help with Scalar-valued function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Scalar-valued function

Author

Topic

cmowbray
Starting Member

5 Posts

Posted - 2010-02-04 : 10:08:08

Sorry this post should be in the sql 2005 section. I would greatly appreciate any help you can give me with the syntax of the following scalar-valued function. I am basicly trying to find out what the latest date is between 3 dates and return the value @LatestDate which I then want to add to a column in an already existing table called tbl_Adjustment. At present I have the following syntax and I am getting an error stating Incorrect syntax near '>'.

CREATE FUNCTION [dbo].[GetLatestDate]
(
@Project_ID int,
@Adjustment_ID int,
@LatestDate datetime
)
RETURNS Datetime
AS
BEGIN

RETURN CASE @LatestDate

WHEN SMT_Decision_Date >= DFP_Approval_Date And SMT_Decision_Date >= Ministerial_Approval_Date THEN SMT_Decision_Date
WHEN DFP_Approval_Date Is Null AND Ministerial_Approval_Date Is Null Then SMT_Decision_Date
WHEN SMT_Decision_Date >= DFP_Approval_Date AND Ministerial_Approval_Date Is Null Then SMT_Decision_Date
WHEN DFP_Approval_Date>= SMT_Decision_Date AND Ministerial_Approval_Date Is Null Then DFP_Approval_Date
WHEN Ministerial_Approval_Date >= SMT_Decision_Date And DFP_Approval_Date Is Null Then Ministerial_Approval_Date
WHEN SMT_Decision_Date>= Ministerial_Approval_Date And DFP_Approval_Date Is Null Then SMT_Decision_Date
WHEN DFP_Approval_Date>= SMT_Decision_Date AND DFP_Approval_Date >= Ministerial_Approval_Date THEN DFP_Approval_Date
WHEN Ministerial_Approval_Date>= SMT_Decision_Date AND Ministerial_Approval_Date >= DFP_Approval_Date THEN Ministerial_Approval_Date
ELSE NULL

END

FROM tbl_Adjustments

WHERE Project_ID = @Project_ID And Adjustment_ID = @Adjustment_ID

END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 10:43:34

[code]
CREATE FUNCTION [dbo].[GetLatestDate]
(
@Product_ID int,
@Adjustment_ID int
)
RETURNS Datetime
AS
BEGIN
DECLARE @LatestDate datetime
SELECT @LatestDate= CASE

WHEN SMT_Decision_Date >= DFP_Approval_Date And SMT_Decision_Date >= Ministerial_Approval_Date THEN SMT_Decision_Date
WHEN DFP_Approval_Date Is Null AND Ministerial_Approval_Date Is Null Then SMT_Decision_Date
WHEN SMT_Decision_Date >= DFP_Approval_Date AND Ministerial_Approval_Date Is Null Then SMT_Decision_Date
WHEN DFP_Approval_Date>= SMT_Decision_Date AND Ministerial_Approval_Date Is Null Then DFP_Approval_Date
WHEN Ministerial_Approval_Date >= SMT_Decision_Date And DFP_Approval_Date Is Null Then Ministerial_Approval_Date
WHEN SMT_Decision_Date>= Ministerial_Approval_Date And DFP_Approval_Date Is Null Then SMT_Decision_Date
WHEN DFP_Approval_Date>= SMT_Decision_Date AND DFP_Approval_Date >= Ministerial_Approval_Date THEN DFP_Approval_Date
WHEN Ministerial_Approval_Date>= SMT_Decision_Date AND Ministerial_Approval_Date >= DFP_Approval_Date THEN Ministerial_Approval_Date
ELSE NULL

END

FROM tbl_Adjustments

WHERE Project_ID = @Project_ID And Adjustment_ID = @Adjustment_ID

RETURN @LatestDate
END
GO

and call like
SELECT dbo.[GetLatestDate](first param value,second param value)
[/code]