joining temp table and user-define function problem

Question

Post reply

joining temp table and user-define function problem

johnsql-193053

SSCommitted

Points: 1657
More actions
March 11, 2006 at 7:15 am

#116430

Folks,
I have 1 stored procedure and 1 function which each returns a table or record set. In my SQL script, I create a temp table (#tgstinfo) to store the return results from the stored procedure. And then I use that temp table to inner join with a function (dbo.fn_GetFamilyMembersOfCustomer()) that also returns a record set. However, I get an error at the following script segment when I inner join the temp table and udf [error highlighted at t.studentID on the line of "...from dbo.fn_GetFamilyMembersOfCustomer(@companyID , t.studentID , 3 , 4)..." ]
select t.*, w.RelativeFirstName as pFirstName, w.RelativeLastName as pLastName
from #tgstinfo t
left join (select top 1 RelativeFirstName, RelativeLastName, studentID
from dbo.fn_GetFamilyMembersOfCustomer(@companyID , t.studentID
, 3 , 4 )) as w
on w.studentID = t.studentID
-- and I get the error in QA
Server: Msg 170, Level 15, State 1, Procedure aagGetGPATranscript, Line 206
Line 206: Incorrect syntax near 't'.
Thanks for your ideas in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You cannot use set of values as a parameter for UDF.

t.StudentId is not a value, it's a set of values.

You need rewrite the function to have only @CompanyId as a parameter because you already have SudentId in result set.

select t.*, w.RelativeFirstName as pFirstName, w.RelativeLastName as pLastName

from #tgstinfo t

left join dbo.fn_GetFamilyMembersOfCustomer(@companyID , 3 , 4 ) as w

on w.studentID = t.studentID

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 2

Thanks Mr. or Ms 500,

I agree with you to pass a value for udf dbo.fn_GetFamilyMembersOfCustomer(). But if so, then the left join table #tgstinfo can associate with all set of records that loses the filetering of studentID. Acutally, I want one studentID-specified row on the temp table #tgstinfo joining with one row of udf #tgstinfo who has the specified studentID matches; each unique student from the left joining table #tgstinfo associate with 1 row from the right table returned by udf dbo.fn_GetFamilyMembersOfCustomer().

Details of my udf:

-----------------

CREATE function dbo.fn_GetFamilyMembersOfCustomer(@companyID int, @givenCustomerID int, @entityTemplateGivenCustomerID int,

@entityTemplateIDRelatives int)

returns table

AS

/*

20060303 Trong made initial verion to return family members of a customer like parent, sibling, grandparent, spouse.

e.g. if @entityTemplateGivenCustomerID= 3 (student) and @entityTemplateIDRelative = 3 (student's sibling)

if @entityTemplateGivenCustomerID= 3 (student) and @entityTemplateIDRelative = 4 (student's parents)

if @entityTemplateGivenCustomerID= 4 (parent or spouse) and @entityTemplateIDRelative = 4 (parent or spouse), and so on.

EntityTemplateID is found in table "CustomerTy"

*/

return

select ct.EntityTemplateID, w.RelationTy , ct.description , cu.customerID, cu.customerStatusTy, cu.lastName as RelativeLastName, cu.FirstName as RelativeFirstName,

w.description as relationWithStudent, w.FromCustomerID as studentID

from customer cu

inner join CustomerTy ct on ct.companyID = cu.companyID and ct.customerTy = cu.customerTy

inner join ( select cr4.companyID, cr4.RelationTy, cr4.FromCustomerID, cr4.ToCustomerID, crt4.description

from CustomerRelation cr4 inner join CustomerRelationTy crt4 on crt4.companyID= cr4.companyID and crt4.RelationTy=cr4.RelationTy

where cr4.companyID = @companyID and cr4.FromCustomerID = @givenCustomerID ) w

on w.companyID = cu.companyID and cu.customerID = w.ToCustomerID

where cu.companyID = @companyID

and ct.EntityTemplateID = @entityTemplateIDRelatives

and w.RelationTy in (

select distinct cr2.RelationTy from CustomerRelation cr2

inner join CustomerRelationTy crt2 on crt2.companyID = cr2.companyID and crt2.RelationTy=cr2.RelationTy

where cr2.companyID = @companyID and cr2.FromCustomerID in (select cu3.customerID from customer cu3

inner join CustomerTy ct3 on ct3.companyID = cu3.companyID and ct3.customerTy = cu3.customerTy

where cu3.companyID = @companyID and ct3.EntityTemplateID = @entityTemplateGivenCustomerID

&nbsp

)

Sergiy SSC Guru Points: 110208 More actions · Answer 3

It's better to use a view in this case.

Function is just a wrong idea.

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 4

Sergiy,

I got you. Thanks for your kind explanations and suggestions.

BTW, if I do not use function, then view and stored procedure is used. what is your con and pro for using VIEW and STORED PROCEDURE?

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 5

It's really incompartible things.

It's like to ask "what is your con and pro for using CAR and BOAT?"

As you can see UDF just does not work where VIEW should be used. Same for SP.

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 6

Sergiy,

Thanks. I am trying to use a VIEW instead of a UDF. Do we need to re-compile a view in which relevant tables creating the view is changed (structure, increased rows, etc.)?