Invalid length parameter passed to the LEFT or Sub

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 2008 Forums

Transact-SQL (2008)

Invalid length parameter passed to the LEFT or Sub

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-09-06 : 10:14:16

[code]
Getting the following error:
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

With some recordid input parameters it works fine, with some getting this error:

How can i increase the length parameter passed to the left or substring?

ALTER FUNCTION [dbo].[getNewRecipientsInfo](@ModuleID integer, @ModuleName VARCHAR(10), @Locale VARCHAR(20), @DistType VARCHAR(5))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
DECLARE @iDateFormat varchar(8)

SET @s=''
SELECT @s=@s + [Name] + ' - '
+ case when @Locale = 'English'
then CONVERT(varchar(10),sentdate, 101)
else CONVERT(varchar(10),sentdate, 103)
end + ' - ' + case
charindex('.',REVERSE(LEFT(REVERSE(filename),CHARINDEX('\', REVERSE(filename),1)-1)) )
when 0 then 'None' else REVERSE(LEFT(REVERSE(filename),CHARINDEX('\', REVERSE(filename),1)-1)) end + char(13) + char(13)
from TAB_Recipients
where ModuleRecordID = @ModuleID and ModuleName = @ModuleName and RecipientType = @DistType
If @s >''
BEGIN
SELECT @s = left(@s, len(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END

[/code]

Thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:10:22

replace
LEFT(REVERSE(filename),CHARINDEX('\', REVERSE(filename),1)-1)

with
LEFT(REVERSE(filename)+'\',CHARINDEX('\', REVERSE(filename)+ '\',1)-1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources