Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: Getting errors I don't understand within Create Function

    Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:
    Code:
    create function DBO.NumValue
    -- This function will get the numbers from the front of a field
    --   and return the value of those numbers in a numeric data type
    	(@mNumInput as charvar(100))
    RETURNS	numeric
    AS
    BEGIN
      declare @x as tinyint
      declare @x1 as tinyint
      SET @x = 1
      WHILE IsNumeric(SubString(@mNumInput, @x, 1))
        BEGIN
          SET @x1 = @x
          SET @x = @x + 1
            CONTINUE
        END
        If @x1 > 0
            BEGIN
    	RETURN CAST(LEFT(@mNumInput, @x1), Numeric
            END
    END
    Here are the two error messages I'm getting from this function.
    Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 12
    Incorrect syntax near the keyword 'BEGIN'.
    Server: Msg 156, Level 15, State 1, Procedure NumValue, Line 20
    Incorrect syntax near the keyword 'END'.
    I have no idea what these two error messages mean.
    TIA,

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by GolferGuy
    Here is the function I'm trying to write. The purpose is to replace MS Access Val() function. I'm not finished with the logic, I'm just trying to get this much to work now. Here is the function:
    Code:
    create function DBO.NumValue
    -- This function will get the numbers from the front of a field
    --   and return the value of those numbers in a numeric data type
    	(@mNumInput as charvar(100))
    RETURNS	numeric
    AS
    BEGIN
      declare @x as tinyint
      declare @x1 as tinyint
      SET @x = 1
      WHILE IsNumeric(SubString(@mNumInput, @x, 1))
        BEGIN
          SET @x1 = @x
          SET @x = @x + 1
            CONTINUE
        END
        If @x1 > 0
            BEGIN
    	RETURN CAST(LEFT(@mNumInput, @x1), Numeric
            END
    END
    Here are the two error messages I'm getting from this function. I have no idea what these two error messages mean.
    TIA,
    The error messages mean you have syntax errors in your function definition.

    You've got a few issues:

    1. there's no such type as charvar. I think you mean varchar
    2. a while condition needs to evalulate to a boolean. but isnumeric returns 1 or 0 (an int). so you need this: WHILE IsNumeric(SubString(@mNumInput, @x, 1)) = 1
    3. your cast at the end has syntax errors.
    4. you don't return a value at the end if the IF isn't satisfied. all functions must return a value. Here's your corrected function:

    Code:
    create function DBO.NumValue
    -- This function will get the numbers from the front of a field
    --   and return the value of those numbers in a numeric data type
    	(@mNumInput as varchar(100))
    RETURNS	numeric
    AS
    BEGIN
      declare @x as tinyint
      declare @x1 as tinyint
      SET @x = 1
      WHILE IsNumeric(SubString(@mNumInput, @x, 1)) = 1
        BEGIN
          SET @x1 = @x
          SET @x = @x + 1
            CONTINUE  -- continue isn't necessary here, but whatever.
        END
        If @x1 > 0
        BEGIN
            RETURN CAST(LEFT(@mNumInput, @x1) as Numeric)
        END
        return -1 -- if no digits found at the beginning of the string
    END
    sample usage:

    select dbo.NumValue('asdf') -- returns -1
    select dbo.NumValue('555asdf') -- returns 555
    Last edited by jezemine; 01-07-07 at 21:58.

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    jezemine,
    Thank you VERY much for your reply. I'm a VBA programmer, so this T-SQL coding language is still a little awkward, therefore, this question:
    WHILE IsNumeric(SubString(@mNumInput, @x, 1)) = 1
    BEGIN
    SET @x1 = @x
    SET @x = @x + 1
    CONTINUE -- continue isn't necessary here, but whatever.
    END
    You said the Continue is not necessary here, but I thought the Continue was the "Loop back to the Where statement" command. How else do you get the "Where" to loop?
    Thanks for your patience!

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it will loop from the point where you have END.

    you only use continue if you want to jump back to the beginnign of the loop before END, that is, if you have statements between continue and end that you don't want to be executed on a particular pass thru the loop.

    Certainly they have while loops in VB, don't they?

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Of course there are loops in VB, but with differing syntax. Within VB, there is no equivilent to "Continue" in T-SQL. Rather than using a command statement such as "Continue", in VB if there are lines after a certain point that should not be executed, that would be done with an If statement. There is only one Loop point in VB. So, this was not a conceptual question (what is a loop?) but a syntax question because of a new language. And, this is the first language for me that uses the concept of a Begin/End I have seen enough of Java and C to know that both of them have that same concept, but as far as writing in a language that uses this concept, this is new and different.
    Thanks a ton for your help and I will be asking some more questions, soon.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    so in VB they have while loops, but no way to go back to the beginning of the loop early?

    I know nothing of VB, my question was an honest one.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sample VB While Loop:
    While TestIsTrue
    i = i + 1
    If i > 5 then
    j = j + 2
    End If
    WEnd 'End of While, therefore loop until TestIsTrue becomes equal to False.
    Same While loop in T-SQL (as I understand it)
    While @TestIsTrue
    Begin
    @i = @i + 1
    If @i <= 5 Continue
    @j = @j + 2
    End

    I'm not sure I got the T-SQL syntax correct, but I tried.
    And yes, I know there is no way out of my loop, but I really don't think it was that important.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i see.

    In T-SQL, it's more similar to what you have in C/C++ (you can use continue to jump to the top, or break to break out)

    For flow control in sql, a good resource is BOL:

    http://msdn2.microsoft.com/en-us/library/ms189826.aspx
    http://msdn2.microsoft.com/en-us/library/ms178642.aspx (funny thing about the first example on this page is they have a noop continue just like you had )

Posting Permissions

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