November 26, 2008 at 3:46 pm
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.
November 26, 2008 at 7:11 pm
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