how to change a scalar valued function to inline

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to change a scalar valued function to inline

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-05-17 : 19:08:25

How can I change the following scalar valued function to an inline function to improve performance. Thanks in advance
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

sarah

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-18 : 13:13:11

I think this is what you're looking for.
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-18 : 14:35:22

Here's another way to concatenate, and it's inline:

CREATE FUNCTION [dbo].[fn_InstructorsEmailsbySectionID] (@SectionID INT) RETURNS TABLE 
AS RETURN (
SELECT COALESCE(STUFF(
	(SELECT N';' + 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 FOR XML PATH(''))
	, 1, 1, N''), 'Faculty') )

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-05-19 : 22:13:47

Thanks a lot for all of you

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-05-19 : 22:15:59

Thanks a lot for all of you

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources