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,
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 (
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
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ','
AND ISNULL(N,0) > 0
SELECT ROW_NUMBER() OVER (ORDER BY N) + @StartIndex,
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.