Is this an efficient way to create a comma string

[anthonykallay]

Hi there,

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

MY SP:
CREATE PROCEDURE sp_Jobs_GetJobs
AS
BEGIN
SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
AS location, salary, summary, logo
FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
ORDER BY j.Inserted DESC

END
GO
--------------------------------------------
MY Function:
CREATE FUNCTION fn_GetJobLocations (@JobID int)

RETURNS varchar(5000) AS
BEGIN
DECLARE @LocList varchar(5000)
SELECT @LocList = COALESCE(@LocList + ', ','') + ll.location_name
FROM Jobs_Locations l inner join List_Locations ll on
ll.LocationID = l.LocationID
WHERE l.JobID = @JobID
RETURN @LocList

END

Any help or guidance much appreciated...

[Erland Sommarskog]

(an***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

I can't speak to sql 2005, but anything previous, I'd TOTALLY support
what Mr. Sommarskog said.

Use a cursor. Much better in the long run, medium run, and short term.
Also, be sure to document what it is the INTENDED purpose of the sproc
is!!!