none
Msg 102, Level 15, State 1 Error

    Question

  •  

    I am writing a user defined function but I am receiving an error message that I can figure out.  Any answers out there?

     

    Msg 102, Level 15, State 1, Procedure ufn_GetStreetNumber, Line 25

    Incorrect syntax near '@StreetNum'.

     

    -- ---------------------------------------------------------------------------------

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Description:  Parses out the Street number for Address field

    -- =============================================

    CREATE FUNCTION dbo.ufn_GetStreetNumber

    (

              -- Add the parameters for the function here

              @StreetAddr varchar(50)

    )

    RETURNS int

    AS

    BEGIN

              -- Declare the return variable here

              DECLARE @StreetNum varchar(50);

              DECLARE @iIter smallint, @iNoOfChars smallint, @iStreetNo int;

     

              SET @StreetNum=NULL;

              SET @iStreetNo=0;

              SET @iNoOfChars=1;

              SET @iIter=1;

     

              -- Add the T-SQL statements to compute the return value here

                       WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1

    @StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)

                                 @iIter=@iIter+1

                       BREAK

                                 @iStreetNo=@StreetNum    

     

              -- Return the result of the function

              RETURN @iStreetNo

     

    END

    GO

     

     

    Monday, December 17, 2007 1:34 PM

Answers

  • Add set as mentioned below

     

        WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1

    Set @StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)

                                Set @iIter=@iIter+1

                       BREAK

                                Set @iStreetNo=@StreetNum    

     

     

    Madhu

    Monday, December 17, 2007 1:40 PM

All replies

  • Add set as mentioned below

     

        WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1

    Set @StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)

                                Set @iIter=@iIter+1

                       BREAK

                                Set @iStreetNo=@StreetNum    

     

     

    Madhu

    Monday, December 17, 2007 1:40 PM
  • But be aware of the *evil* isNumeric :-)

    http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Monday, December 17, 2007 5:24 PM