|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196,
Visits: 1,319
|
|
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.
|
|
|
|
|
SSCrazy Eights
        
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
|
|
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.
-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."
|
|
|
|