Is this an efficient way to create a comma string

[anthonykallay@hotmail.com]

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_GetJobLo cations(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_GetJobLocati ons (@JobID int)

RETURNS varchar(5000) AS
BEGIN
DECLARE @LocList varchar(5000)
SELECT @LocList = COALESCE(@LocLi st + ', ','') + ll.location_nam e
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]

Re: Is this an efficient way to create a comma string

(anthonykallay@ hotmail.com) writes:[color=blue]
> 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???[/color]

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(OrdId List, 1, datalength(OrdI dList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarcha r(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList )
go
[color=blue]
> CREATE PROCEDURE sp_Jobs_GetJobs[/color]

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@sommarsk og.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

[Doug]

Re: Is this an efficient way to create a comma string

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