Posted by anthonykallay on 12/05/05 19:11

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

[Back to original message]