function

I'm trying to create a function in sql but its not working, which accepts a parameter of @year int
returns a data type of "money"
use the @year to return the amount from Ref_table where the @year falls between begin_year and end_year. if there is no value, return the amount from
Ref_table with the highest end_year that is less than @year*/
here is my code
CREATE FUNCTION fn_year(
@year int
)
RETURNS money
as
begin

declare @begin_year int
declare @end_year int
declare @amount money

select @begin_year = begin_year,
@end_year = end_year
from DBO.REF_Table
if @year between year(@begin_year) and year(@end_year)
select @amount = amount
from dbo.REF_Table
end
else if @amount is null
begin
select @amount = amount
from dbo.REF_Table
where max @end_year
and @end_year < @year
end
End
GO

Please, use Code tags(with proper code indentations) when posting code snippets. Otherwise your code is absolutely unreadable.
Use debugger (Alt+F5) to debug this function to see where and why goes wrong.

Quote:

---

Originally Posted by VictorN

Please, use Code tags(with proper code indentations) when posting code snippets. Otherwise your code is absolutely unreadable.
Use debugger (Alt+F5) to debug this function to see where and why goes wrong.

---

Thanks, and sorry for the non indentations, I'm new to this. My SQL server won't allow (Alt+ F5) to debug.

PHP Code:

---

"CREATE FUNCTION fn_year(
    @year int 
)
RETURNS money
as
begin

    declare @begin_year int
    declare @end_year int
    declare @amount money
    
    select  @begin_year = begin_year,
                @end_year = end_year
    from DBO.REF_Table
    if @year between year(@begin_year) and year(@end_year)
        select @amount = amount
        from dbo.REF_Table
    end
    else if @amount is null
    begin
        select @amount = amount
        from dbo.REF_Table
        where max @end_year
        and @end_year < @year
    end
End
GO" 


---

still not working.

Hi I had the code indented, but when it posts, it goes back to align left?

Quote:

---

Originally Posted by salman afzal

Hi I had the code indented, but when it posts, it goes back to align left?

---

You have to use Code tags. See Announcement: Before you post....

About debugging:
http://msdn.microsoft.com/en-us/libr...(v=vs.90).aspx