May 17, 2012 at 5:11 pm
Please I need help in changing the following scalar valued function to an inline one to improve performance. Thanks a lot.
I have several similar scalar valued function so if you helped me in this one this will help me a lot with other ones.
create FUNCTION [dbo].[fn_InstructorsEmailsbySectionID]
(
@SectionID int
)
RETURNS nVARCHAR(2000)
AS
BEGIN
declare @num int
DECLARE @Instructors nVARCHAR(2000)
SET @Instructors = ''
declare @id table (Email nvarchar (60))
insert into @id
(Email)
(SELECT Email
FROM SP.SectionInstructor
INNER JOIN
sp.[User]
ON
SP.SectionInstructor.InstructorID=sp.[User].UserID
where SP.SectionInstructor.SectionID =@SectionID
and instructorrole=1
)order by firstname,lastname
select @num =count(email) from @id
if @num =0
select @instructors='Faculty'
else
begin
SELECT @Instructors = ISNULL(@Instructors, '') + me.email+ '; '
from(SELECT email
FROM @id
)as me
SET @Instructors = LEFT(@Instructors, LEN(@Instructors) - 1)
IF LEN(@Instructors) > 1
SET @Instructors = LEFT(@Instructors, LEN(@Instructors))
end
RETURN @Instructors
END
May 17, 2012 at 5:42 pm
Try something like that:
SELECT ISNULL(SUBSTRING(( SELECT '; ' + u.Email
FROM SP.SectionInstructor as i
INNER JOIN SP.User as u ON u.UserID = i.InstructorID
where i.SectionID = @SectionID and i.IntructorRole = 1
order by u.FirstName, u.LastName
FOR XML PATH('')
), 3, 200000), 'Faculty')
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply