INVALID LENGTH PARAMETER PASSED....

I have the follwoing stored procedure:

ALTER procedure [dbo].[up_GetExecutionContext](
@ExecutionGUID int = null
) as
begin
set nocount on

declare @s varchar(500)
declare @i int

set @s = ''
select @s = @s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@ExecutionGUID is null or PackageStep.packagerunid =
@ExecutionGUID)
order by 1
) as x

set @i = len(@s)
select case @i
when 500 then left(@s, @i - 3) + '...' -- If string is too long then
terminate with '...'
else left(@s, @i - 1) -- else just remove the final comma
end as 'Context'

set nocount off
end --procedure
GO

When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:

Invalid length parameter passed to the SUBSTRING function.

There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.

Any suggestions would be greatly appreciated.

Thanks!!

Re: INVALID LENGTH PARAMETER PASSED....

On Jun 4, 12:43 pm, ansonee wrote:
> I have the follwoing stored procedure:
>
> ALTER procedure [dbo].[up_GetExecutionContext](
> @ExecutionGUID int = null
> ) as
> begin
> set nocount on
>
> declare @s varchar(500)
> declare @i int
>
> set @s = ''
> select @s = @s + EventType + ',' -- Dynamically build the list of
> events
> from(
> select distinct top 100 percent [event] as EventType
> from dbo.PackageStep
> where (@ExecutionGUID is null or PackageStep.packagerunid =
> @ExecutionGUID)
> order by 1
> ) as x
>
> set @i = len(@s)
> select case @i
> when 500 then left(@s, @i - 3) + '...' -- If string is too long then
> terminate with '...'
> else left(@s, @i - 1) -- else just remove the final comma
> end as 'Context'
>
> set nocount off
> end --procedure
> GO
>
> When I run this and pass in a value of NULL, things work fine. When I
> pass in an actual value (i.e. 15198), I get the following message:
>
> Invalid length parameter passed to the SUBSTRING function.
>
> There is no SUBSTRING being used anywhere in the query and the
> datatypes look okay to me.
>
> Any suggestions would be greatly appreciated.
>
> Thanks!!

increase the value of @s from 500 to 5000 maybe and test it ?

Re: INVALID LENGTH PARAMETER PASSED....

ansonee (ansonee@yahoo.com) writes:
> set @s = ''
> select @s = @s + EventType + ',' -- Dynamically build the list of
> events
> from(
> select distinct top 100 percent [event] as EventType
> from dbo.PackageStep
> where (@ExecutionGUID is null or PackageStep.packagerunid =
> @ExecutionGUID)
> order by 1
> ) as x

I'm afraid that this relies on undefined behaviour. It may produce what
you want today. It might not tomorrow. If you are on SQL 2000, you
will need to run a cursor. On SQL 2005 there exists an option with
XML. See SQL Server MVP Antith Sen's article on
http://www.projectdmx.com/tsql/rowconcatenate.aspx for more information.

> set @i = len(@s)
> select case @i
> when 500 then left(@s, @i - 3) + '...' -- If string is too
> long then
> terminate with '...'
> else left(@s, @i - 1) -- else just remove the final comma
> end as 'Context'
>
> set nocount off
> end --procedure
> GO
>
> When I run this and pass in a value of NULL, things work fine. When I
> pass in an actual value (i.e. 15198), I get the following message:
>
> Invalid length parameter passed to the SUBSTRING function.
>
> There is no SUBSTRING being used anywhere in the query

No, but there is LEFT, which is just a shortcut for SUBSTRING.

More to the point, you have failed to handle the case that the query
does not find any events, and @i is the empty string.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx