Function Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Function Help

Author

Topic

AndyVasey
Starting Member

2 Posts

Posted - 2010-07-14 : 16:57:42

I have a table called actuarial_load_dates, containing only 1 column called load_dates. I need to write some sql that pulls all of the dates in the first column, and the date prior to the date in the first column in a second column. I wrote a function as follows:

CREATE FUNCTION PriorMonthDate
(@P_InDate DATETIME)

RETURNS DATETIME
AS
BEGIN
declare @P_OutDate datetime

case @P_InDate when '2003-12-31' then
@P_OutDate = '2003-01-01'
else
select @P_OutDate = max(load_date) from hades_actuarial.dbo.actuarial_load_dates where load_date < @P_InDate
end

return @P_OutDate
end

but I am getting the following errors:

Msg 156, Level 15, State 1, Procedure PriorMonthDate, Line 14 Incorrect syntax near the keyword 'case'.
Msg 137, Level 15, State 1, Procedure PriorMonthDate, Line 17 Must declare the scalar variable "@P_OutDate".
Msg 137, Level 15, State 2, Procedure PriorMonthDate, Line 20 Must declare the scalar variable "@P_OutDate".

Can anyone help?

Thanks in advance

Andy

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-14 : 17:23:19

Maybe this?

SET @P_OutDate  =
	case 
		when @P_InDate = '2003-12-31' then '2003-01-01'
		else
			select max(load_date) from hades_actuarial.dbo.actuarial_load_dates where load_date < @P_InDate
	end

AndyVasey
Starting Member

2 Posts

Posted - 2010-07-15 : 00:19:51

I still get the following errors:

Msg 156, Level 15, State 1, Procedure PriorMonthDate, Line 24
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Procedure PriorMonthDate, Line 28
Incorrect syntax near the keyword 'return'.
Msg 102, Level 15, State 1, Procedure PriorMonthDate, Line 29
Incorrect syntax near 'end'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-16 : 11:35:52

[code]SET @P_OutDate =
case
when @P_InDate = '2003-12-31' then '2003-01-01'
else
(select max(load_date) from hades_actuarial.dbo.actuarial_load_dates where load_date < @P_InDate)
end
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources