|
|
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...
Navigation:
[Reply to this message]
|