Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    12

    Unanswered: Newbie needs help with User Defined Functions

    Well I have never worked with SQL Server before and I am trying to figure things out on my own. I have references that I have been using to teach myself how to use SQL Server. I have run into a problem thou. I have to use an if statement in this User Defined Function. But I'm getting compiling errors on the IFs, ELSE IFs and the THENs. Can someone please tell me what is wrong with the syntax of my function. I have pasted it below. Any help given will be very much appreciated. Thanks in advance.

    CREATE FUNCTION pbaweb.MonthTransForm
    (
    @MonthNumber CHAR(2)
    )
    RETURNS CHAR

    AS

    BEGIN

    IF(SELECT PayMonth from GroupActivity) = "1"

    THEN

    DECLARE @MonthTitle VARChar(15)
    Set @MonthTitle = "January"
    RETURN @Monthtitle;

    ELSE IF (SELECT PayMonth from GroupActivity) = "2" THEN
    set @MonthTitle = "Feburary"
    RETURN @Monthtitle;
    ELSE IF (SELECT PayMonth from GroupActivity) = "3" THEN
    set @MonthTitle = "March"
    RETURN @Monthtitle;
    ELSE IF (SELECT PayMonth from GroupActivity) = "4" THEN
    set @MonthTitle = "April"
    RETURN @Monthtitle;
    ELSE IF (SELECT PayMonth from GroupActivity) = "5" THEN
    set @MonthTitle = "May"
    RETURN @Monthtitle;
    END IF;
    END;

  2. #2
    Join Date
    Apr 2002
    Location
    VA
    Posts
    18

    Syntax error of IF

    Hi,

    Check BOL for IF ELSE syntax. THEN is not part of IF statement in SQL

    IF Boolean_expression
    { sql_statement | statement_block }
    [ ELSE
    { sql_statement | statement_block } ]


    Best of luck
    Deepak Khattar

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    DeepakKhattar is correct, there is no THEN, you would use BEGIN with END.

    IF Boolean_expression BEGIN
    { sql_statement | statement_block }
    END

    However in your case you should look at the CASE statement
    Code:
     SELECT @MonthTitle  = CASE
    			WHEN PayMonth = "1" THEN "January"
    			WHEN PayMonth = "2" THEN "February"
    			WHEN PayMonth = "3" THEN "March"
    			.
    			.
    			.
    			ELSE "December"
    		END
    FROM GroupActivity
    MCDBA

Posting Permissions

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