• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

Invalid length parameter passed to the SUBSTRING function

I have a store procedure, attached.
for eg, I am search for record whose record name='7-Eleven'
If I put
@whereClause = N'([Company] LIKE ''%7%'')'
then the sp run fine.
But If i put @whereClause = N'([Company] LIKE ''%7 cc77%'')'   (in case if some user mis-type)
then the sp gave the error msg say:
Invalid length parameter passed to the SUBSTRING function.
(1 row(s) affected)

Actually, in this case what I expected is no record return since Company Name can not be matched. But why we see the error ?

How to fix it.

Thank you in advance
 
ALTER PROCEDURE [dbo].[sp_GenerateUniqueRetailerList]
	-- Add the parameters for the stored procedure here
	@whereClause varchar(MAX)
AS
BEGIN
   SET QUOTED_IDENTIFIER OFF
   SET ANSI_NULLS ON
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SQL varchar(MAX)

	SET @SQL = 'DECLARE @List varchar(2000)'
	SET @SQL = @SQL + 'SET @List = '''';'
	SET @SQL = @SQL + 'SELECT @List = @List + Cast(_PK_RetailerContact As varchar(5)) + '','' FROM (SELECT MIN(_PK_RetailerContact) AS _PK_RetailerContact, MIN(Contact_First_Name) AS Contact_First_Name, MIN(Contact_Last_Name) AS Contact_Last_Name FROM (SELECT * FROM dbo.RetailerContact WHERE ' + @whereClause + ') AS FilteredDetail GROUP BY Contact_First_Name, Contact_Last_Name) AS GroupByQuery;'
	SET @SQL = @SQL + 'SET @List = SUBSTRING(@List, 1, Len(@List) - 1);'
	SET @SQL = @SQL + 'SELECT @List As ''List'';'
   
    --PRINT @SQL
	EXEC(@SQL)

END

Open in new window

0
FinghtingMiao
Asked:
FinghtingMiao
  • 3
  • 3
  • 3
  • +1
1 Solution
 
pivarCommented:
Hi,

I think the problem is that @List is empty.
With @List = '' you  get Len(@List) - 1 = -1 in SET @List = SUBSTRING(@List, 1, Len(@List) - 1);

/peter
0
 
Craig WagnerSoftware ArchitectCommented:
It seems pretty obvious to me why you're getting the error. If no rows are returned then @List contains nothing. When it hits the next line.

SET @SQL = @SQL + 'SET @List = SUBSTRING(@List, 1, Len(@List) - 1);'

Len(@List) = 0, and 0 - 1 = -1, which is an invalid length parameter.
0
 
FinghtingMiaoAuthor Commented:
But how come the @List is empty happen? still confusing.
Thank you.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
FinghtingMiaoAuthor Commented:
Sorry, I did not see CraigWagner's comments. NOw I understand why @ list can be empty.
Like this case, how can we fix it?
0
 
pivarCommented:
Since there is no matches in your select, @list isn't set to anything
0
 
pivarCommented:
Try changing to

       SET @SQL = @SQL + 'IF len(@List) > 1 SET @List = SUBSTRING(@List, 1, Len(@List) - 1);'
0
 
Kevin CrossChief Technology OfficerCommented:
Usually for something like this, I would leave the initial variable NULL.

DECLARE @List varchar(2000)'
-- i.e., drop this part == > SET @SQL = @SQL + 'SET @List = '''';'
-- then in my query, use coalesce() or isnull() to add the comma
SET @SQL = @SQL + 'SELECT @List = COALESCE(@List + '','', '''') + Cast(_PK_RetailerContact As varchar(5)) ...'
-- this does not fix what is causing no rows, but you can drop the next line:
-- SET @SQL = @SQL + 'SET @List = SUBSTRING(@List, 1, Len(@List) - 1);'
-- which eliminates the problem with invalid length passed to substring() error
0
 
Kevin CrossChief Technology OfficerCommented:
If you do not want NULL in the last part, you can wrap that with COALESCE():
SET @SQL = @SQL + 'SELECT COALESCE(@List, '') As "List";'

Open in new window

Note: "double quotes" are column identifiers, so you can use that instead of ''two single quotes together''.
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry for the extra comment, but I just saw "Actually, in this case what I expected is no record return since Company Name can not be matched. But why we see the error ?"

If you want no record returned, then you may want this:
SET @SQL = @SQL + 'SELECT @List As "List" WHERE @List IS NOT NULL;'

Open in new window


Using my other code shown, if there were no rows found then @List would still be NULL here and so you can use that to make no result set come back if the @List is empty.
0
 
FinghtingMiaoAuthor Commented:
That works! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now