Posted by Erland Sommarskog on 12/06/05 00:23

(anthonykallay@hotmail.com) writes:
> I have created a sp and function that returns amongst other things a
> comma seperated string of values via a one to many relationship, the
> code works perfectly but i am not sure how to test its performance.. Is
> this an efficient way to achieve my solution.. If not any suggestions
> how i can improve it.. What are the best ways to check query speed???

Efficient, maybe. Reliable, well-defined and supported, no. What you
have written may work, but it relies on undefined behaviour. My advice
is that you should use a cursor for this, if you are on SQL 2000. No,
that is not effecient, but reliability is more important than performance.

On SQL 2005, there is a set-based way to do this, here demonstrated
with a sample query:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

> CREATE PROCEDURE sp_Jobs_GetJobs

Don't use the sp_ prefix to name your stored procedures. This prefix is
reseved for system objects, and SQL Server first looks in the master
database for these.


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

[Back to original message]