Invalid length parameter passed to the SUBSTRING f

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Invalid length parameter passed to the SUBSTRING f

Author

Topic

hozcarinho
Starting Member

4 Posts

Posted - 2010-02-25 : 14:11:09

hi to everyone, my english is not to good but i will try to explain my problem, well i´m trying to do an SP in thins SP im try to concatenate a group of records in one record, for example:
one invoice have several items like this:
invoice 123
item short
shirt
pant
well im try to concatenate all items in one record something like this: short, shirt, pant
i try to do this with this code:

declare @producto nvarchar(4000)
select @producto = space(4000)

set @producto = ''

select @producto = @producto + rtrim(ltrim(a.id_producto)) + ', '
from fdtrafico a
where a.id_entrada = 'WE0225/10'

--IN THIS SECTION IS WHERE I'M HAVING PROBLEMS
set @producto = SUBSTRING(@producto, len(ltrim(@producto))-1)

PRINT @PRODUCTO

Well it seems when the reference have NULL value is when the error happens, if someone can helpme i will be very grateful,

Sachin.Nand

2937 Posts

Posted - 2010-02-25 : 14:16:39

Sunstring needs 3 parameters.With what I can see you have just passed 2
SUBSTRING(@producto,Missing something here ,len(ltrim(@producto))-1)

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-25 : 14:17:00

substring(@producto,<startposition>,<length>)

But I don't know what you are trying to do...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-25 : 14:17:33

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-02-25 : 14:22:17

quote:
Originally posted by webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

I think this is as close as one can get at SQLTeam.Just 21 seconds

PBUH

hozcarinho
Starting Member

4 Posts

Posted - 2010-02-25 : 14:36:29

you are right im sorry

declare @producto nvarchar(4000)
select @producto = space(4000)

set @producto = ''
select @producto = @producto + rtrim(ltrim(a.id_producto)) + ', '
from fdtrafico a
where a.id_entrada = 'WE0717/10'

set @producto = SUBSTRING(@producto, 1,len(ltrim(@producto))-1)
print (@producto)

Sachin.Nand

2937 Posts

Posted - 2010-02-25 : 14:45:50

So is it working now?

PBUH

hozcarinho
Starting Member

4 Posts

Posted - 2010-02-25 : 14:52:21

no!! that is the problem i just put all the code again because the firts code was wrong

hozcarinho
Starting Member

4 Posts

Posted - 2010-02-25 : 18:24:50

problem resolved the problem was is this sentence
set @producto = SUBSTRING(@producto, 1,len(ltrim(@producto))-1)
in this part a remove the last ',' that i put to separate each word so i rest -1 and that is the problem because when the result is 0 and i try to rest -1 to 0, the result was and error :s so i only have to use a if and ready!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources