CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: function

  1. #1
    Join Date
    Feb 2014
    Posts
    5

    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

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: function

    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.
    Victor Nijegorodov

  3. #3
    Join Date
    Feb 2014
    Posts
    5

    Re: function

    Quote Originally Posted by VictorN View Post
    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.
    Last edited by salman afzal; March 25th, 2014 at 07:09 AM.

  4. #4
    Join Date
    Feb 2014
    Posts
    5

    Re: function

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

  5. #5
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: function

    Quote Originally Posted by salman afzal View Post
    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
    Victor Nijegorodov

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured