Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Odd behaviour with blank CSV parameters Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 3:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
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.
Post #609486
Posted Wednesday, November 26, 2008 7:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #609521
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse