|
|
Posted by Erland Sommarskog on 06/04/07 21:34
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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|