using an inline function in a select statement

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)

using an inline function in a select statement

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-05-19 : 23:15:12

if I want to use an inline function

CREATE FUNCTION dbo.fn_InstructorsEmailsbySectionID (@SectionID int) RETURNS TABLE

AS

RETURN

( SELECT COALESCE(STUFF(( SELECT '; ' + r.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, 2, ''), 'Faculty') AS Instructors

if I want to use it in a select statement

is the following the correct way to do it

select distinct ss.SectionID, ss.SectionStartDate, (select instructors from dbo.fn_InstructorsEmailsbySectionID_inline(ss.SectionID)) as email, S.courseid,S.sectionnumber from Sp.SectionSemester ss inner join SP.Section S on ss.SectionID=S.sectionID where S.sectionstatus != 2 and s.Deleted=0 and ss.SemesterID=@semesterid and s.ParentSection=0

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 00:01:57

nope. your created udf is table valued udf so you cant use udf like this. it should be like


select distinct ss.SectionID, ss.SectionStartDate, f.instructors  as email, S.courseid,S.sectionnumber 
from Sp.SectionSemester ss 
inner join SP.Section S 
on ss.SectionID=S.sectionID 
cross apply dbo.fn_InstructorsEmailsbySectionID_inline(ss.SectionID) f
where S.sectionstatus != 2 
and s.Deleted=0 
and ss.SemesterID=@semesterid 
and s.ParentSection=0

b/w is SP name of schema?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-05-20 : 00:15:47

Thank you so much
Yes sp is a schema name

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 10:57:00

ok..then you're fine

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources