Odd behaviour with blank CSV parameters

  • Hi

    The following code is part of something I'm developing on another thread: http://www.sqlservercentral.com/Forums/Topic607216-149-1.aspx, but Its a distinct problem in itself, which I hope has a simple solution. I'm separating a text list of numbers into float values, and assigning a sequence number to each. The sequence numbers should be, well, in sequence. Occasionally there is a blank parameter, which should be ignored, rather than converted to zero. The code below artificially forces a blank parameter.

    DECLARE @Parameter VARCHAR(8000)

    SET @Parameter = ',0.425932863003029405E-05,0.169551626800036872E-05,-0.854788154819000041E-07,-0.154903893130000000E-06,0.294743374914000000E-05,-0.288437212719999980E-05 ';

    DECLARE @StartIndex INT

    SET @StartIndex = 23

    DECLARE @ELEMENTS TABLE

    ( Number INT,

    eValue VARCHAR(8000))

    SET NOCOUNT ON

    SET @PARAMETER = ','+@PARAMETER+',';

    DECLARE @BIGNUM VARCHAR(MAX);

    -- get 10,000 characters in the @BIGNUM field - should be enough for Tally table

    SET @BIGNUM = REPLICATE(CAST('1234567890' AS VARCHAR(MAX)),1000);

    WITH cteTally AS (

    SELECT TOP(LEN(@BIGNUM))

    ROW_NUMBER() OVER (ORDER BY O.Object_ID) AS N

    FROM Sys.objects AS O, Sys.Columns AS C

    ), Splits AS (

    SELECT N , SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS sValue

    FROM ctetally

    WHERE N < LEN(@Parameter)

    AND SUBSTRING(@Parameter,N,1) = ','

    AND ISNULL(N,0) > 0

    )

    SELECT ROW_NUMBER() OVER (ORDER BY N) + @StartIndex,

    sValue,

    CONVERT(float, sValue)

    FROM Splits

    WHERE ISNULL(sValue,'') <> ''

    The code gives error:

    Msg 536, Level 16, State 5, Line 14

    Invalid length parameter passed to the SUBSTRING function.

    If you remove the final WHERE clause - the code works, but the first converted value is a blank parameter - which I don't want in the result set.

    Now I can fix this by removing blank parameters before they get to CTE - thus eliminating the need for the final WHERE clause - so this is just for general discussion and not a real world urgent problem - but I can't work out WHY I get the error.

  • The problem is the second term of the WHERE clause:

    WHERE N < LEN(@Parameter)

    AND SUBSTRING(@Parameter,N,1) = ','

    AND ISNULL(N,0) > 0

    )

    If N is greater than the length of "@Parameter" then SUBSTRING will return an error.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply