This is driving me absolutley crazy. This function returns ProductIDs stripped out from a memo field based on a check on Product Type (which is also stripped for the checking) and returns a list of ProductIDs based on the type I'm checking for, in other words the @.FileFormat
The error: Invalid length parameter passed to the substring function.
I am passing 2 types of ProductDescriptions to my function below. This function returns ProductIDs by Type, stripped out of a large varchar field. The problem I'm having is that I have 2 checks that check whether the ProductID is MP3 or WAV. The second check is failing.
The 2 types of possible incoming ProductDescriptions for example are:
'These fully-orchestrated royalty free music tracks invoke the spirit of some of the great themes from 1970"s and 1980"s television and film productions and offer majestic brass, string and guitar melodies that will make a memorable addition to projects as background music and production music.<br><br><span class="product-name-no-link">You can also purchase the individual tracks:</span><br><br>01. American Plains <a href="ProductInfo.aspx?ProductID=105234">MP3</a> | <a href="ProductInfo.aspx?ProductID=105235">WAV</a><br>02. Sultry Summer Night <a href="ProductInfo.aspx?ProductID=105236">MP3</a> | <a href="ProductInfo.aspx?ProductID=105237">WAV</a><br>03. Ocean Skyline <a href="ProductInfo.aspx?ProductID=105238">MP3</a> | <a href="ProductInfo.aspx?ProductID=105239">WAV</a><br>04. Wistful Lover <a href="ProductInfo.aspx?ProductID=105240">MP3</a> | <a href="ProductInfo.aspx?ProductID=105241">WAV</a><br>05. Final Choice <a href="ProductInfo.aspx?ProductID=105242">MP3</a> | <a href="ProductInfo.aspx?ProductID=105243">WAV</a><br>06. Fun and Free <a href="ProductInfo.aspx?ProductID=105244">MP3</a> | <a href="ProductInfo.aspx?ProductID=105245">WAV</a><br>07. Wayward Strangers <a href="ProductInfo.aspx?ProductID=105246">MP3</a> | <a href="ProductInfo.aspx?ProductID=105247">WAV</a><br>08. Savored Moments <a href="ProductInfo.aspx?ProductID=105248">MP3</a> | <a href="ProductInfo.aspx?ProductID=105249">WAV</a><br>09. Endless Searcher <a href="ProductInfo.aspx?ProductID=105250">MP3</a> | <a href="ProductInfo.aspx?ProductID=105251">WAV</a><br>10. Bach Piano <a href="ProductInfo.aspx?ProductID=105252">MP3</a> | <a href="ProductInfo.aspx?ProductID=105253">WAV</a><br>11. Fog Bound Mornings <a href="ProductInfo.aspx?ProductID=105254">MP3</a> | <a href="ProductInfo.aspx?ProductID=105255">WAV</a><br>'
OR
'Clapping percussion effects characterize this Hip Hop/Urban piece with train sound effects and strings.<br><br><b>Styles:</b> Dramatic,Reflective,Somber/dark<br><br><b>If you like this track, you can save more than <span style='color=#ff0000'>70%</span> by purchasing it on the following albums:</b><br><a href="ProductInfo.aspx?ProductID=106758">Hip-Hop / Urban</a><br><a href="ProductInfo.aspx?ProductID=106763">Documentary, Film, Television, General Production - Volume 2</a><br><br>Click <a href="ProductInfo.aspx?ProductID=105747">here</a> for the WAV version of this track.'
The current function I'm trying to fix:
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @.Keyword varchar(30),
@.KeywordLen tinyint,
@.ProductID int,
@.Pos smallint,
@.StartPos smallint,
@.EndPos smallint,
@.Result varchar(1000),
@.valid int
SET @.valid = 0
SET @.Keyword = 'ProductInfo.aspx?ProductID='
SET @.KeywordLen = LEN(@.Keyword)
SET @.Result = ''
SET @.Pos = 1
WHILE @.Pos > 0
BEGIN
SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)
If @.Pos > 0
BEGIN
SET @.StartPos = @.Pos + @.KeywordLen
SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat
SET @.valid = 1
IF (len(@.ProductDescription) - @.StartPos) > 19
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat
SET @.valid = 1
IF @.valid = 1
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','
END
SET @.Pos = @.EndPos + 1
END
END
RETURN SUBSTRING(@.Result,1,len(@.Result)-1)
END
The following checks to see whether the ProductID is WAV or MP3 by checking certain chars after each sequence 'ProductInfo.aspx?ProductID='
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat - checks if it's a 'WAV' or 'MP3' based on the first type of incoming ProductDescription passed to this function at any time
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat - checks to see if it's a 'WAV' or 'MP3' ProductID based on the second type of possible incoming ProductDescription.
I found out that the second check fails if there isn't 19 or more chars to check so I tried putting this in:
So I'm not sure how to handle the Nulls in the second IF statement if there are not enough chars to check in the loop for each SUBSTRING(@.ProductDescription, @.EndPos + 19, 3)
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
@.ProductDescription varchar(5500),
@.FileFormatvarchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @.Keyword varchar(30),
@.KeywordLen tinyint,
@.ProductID int,
@.Pos smallint,
@.StartPos smallint,
@.EndPos smallint,
@.Result varchar(1000),
@.valid int
SET @.valid = 0
SET @.Keyword = 'ProductInfo.aspx?ProductID='
SET @.KeywordLen = LEN(@.Keyword)
SET @.Result = ''
SET @.Pos = 1
WHILE @.Pos > 0
BEGIN
SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)
If @.Pos > 0
BEGIN
SET @.StartPos = @.Pos + @.KeywordLen
SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat
SET @.valid = 1
IF (len(@.ProductDescription) - @.StartPos) > 19
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat
SET @.valid = 1
--IF @.valid = 1
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','
END
SET @.Pos = @.EndPos + 1
END
END
RETURN SUBSTRING(@.Result,1,len(@.Result)-1)
END
|||I since then have figured it out by extensive testing and added some stuff so that it will behave...but let me take a look at what you had to suggest also. Thanks for the suggestion though!
ALTER FUNCTION [dbo].[GetProductChildIDs]
(
@.ProductDescription varchar(5500),
@.FileFormat varchar(3)
)
RETURNS varchar(1000)
AS
BEGIN
Declare @.Keyword varchar(30),
@.KeywordLen tinyint,
@.ProductID int,
@.Pos smallint,
@.StartPos smallint,
@.EndPos smallint,
@.Result varchar(1000),
@.ResultToReturn varchar(1000),
@.valid int,
@.MP3Pos int,
@.WavPos int
SET @.valid = 0
SET @.Keyword = 'ProductInfo.aspx?ProductID='
SET @.KeywordLen = LEN(@.Keyword)
SET @.Result = ''
SET @.ResultToReturn = ''
SET @.Pos = 1
WHILE @.Pos > 0
BEGIN
SET @.Pos = CHARINDEX(@.Keyword, @.ProductDescription, @.Pos)
If @.Pos > 0
BEGIN
SET @.StartPos = @.Pos + @.KeywordLen
SET @.EndPos = CHARINDEX('"', @.ProductDescription, @.StartPos + 1)
IF SUBSTRING(@.ProductDescription, @.EndPos + 2, 3) = @.FileFormat
BEGIN
SET @.valid = 1
END
IF SUBSTRING(@.ProductDescription, @.EndPos + 19, 3) = @.FileFormat
BEGIN
SET @.valid = 1
END
IF @.valid = 1
BEGIN
SELECT @.Result = @.Result + SUBSTRING(@.ProductDescription, @.StartPos, @.EndPos - @.StartPos) + ','
END
SET @.Pos = @.EndPos + 1
END
END
IF len(@.Result) > 6
SET @.ResultToReturn = @.ResultToReturn + SUBSTRING(@.Result,1,len(@.Result)-1)
RETURN @.ResultToReturn
END
|||why did you comment out the If @.valid = 1?
