[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Invalid length parameter passed to the SUBSTRING function

Posted on 2011-10-06
10
Medium Priority
?
382 Views
Last Modified: 2012-05-12
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
Comment
Question by:FinghtingMiao
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 36927277
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
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 36927288
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
 

Author Comment

by:FinghtingMiao
ID: 36927306
But how come the @List is empty happen? still confusing.
Thank you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:FinghtingMiao
ID: 36927325
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
 
LVL 22

Expert Comment

by:pivar
ID: 36927331
Since there is no matches in your select, @list isn't set to anything
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 36927333
Try changing to

       SET @SQL = @SQL + 'IF len(@List) > 1 SET @List = SUBSTRING(@List, 1, Len(@List) - 1);'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36927368
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36927376
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36927385
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
 

Author Closing Comment

by:FinghtingMiao
ID: 36927513
That works! Thank you!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question