Calling a stored proc/UDF with results from another

Hi all - I'm sure this is a silly, obvious question, but I'm having some
trouble..
Let's say I have three tables: Events, People and EventsPeople.
EventsPeople is a join-table that represents the people associated with each
event.
I have a GetPerson stored procedure that takes a personId as a parameter and
returns the record for that person. I'm trying to reuse this logic from a
GetPeopleInEvent stored procedure, and I'm not sure how to do this w/o a
cursor:
I'm trying to do this:
select * from people
where personId in
(select personId
from eventspeople
where eventId = @.eventId)
But I want to get rid of the 'select * from people* part and replace it with
something like this:
select *
from Func_Person_GetPerson(personId)
where personId in
(select personId
from eventspeople
where eventId = @.eventId)
where Func_Person_GetPerson is a user-defined function. This, of course,
doesn't work - the parameter 'personId' isn't defined in the FROM clause.
Any ideas on the best way to do this?
Thanks,
PhilWhy not
SELECT p.[Name] FROM People p
INNER JOIN EventsPeople e ON e.PersonID = p.PersonId
WHERE e.eventID = @.eventId
assuming you have a [Name] column in People that stores the full name of the
person. Whatever statements you use in your sp to retreive the name for a
given PersonID, put them in the above to get the name directly in one query,
instead of using a udf.
Also think about using joins instead of sub-queries and the IN clause; you
will likely see much better performance in your queries.
"PMarino" wrote:

> Hi all - I'm sure this is a silly, obvious question, but I'm having some
> trouble..
> Let's say I have three tables: Events, People and EventsPeople.
> EventsPeople is a join-table that represents the people associated with ea
ch
> event.
> I have a GetPerson stored procedure that takes a personId as a parameter a
nd
> returns the record for that person. I'm trying to reuse this logic from a
> GetPeopleInEvent stored procedure, and I'm not sure how to do this w/o a
> cursor:
> I'm trying to do this:
> select * from people
> where personId in
> (select personId
> from eventspeople
> where eventId = @.eventId)
> But I want to get rid of the 'select * from people* part and replace it wi
th
> something like this:
>
> select *
> from Func_Person_GetPerson(personId)
> where personId in
> (select personId
> from eventspeople
> where eventId = @.eventId)
> where Func_Person_GetPerson is a user-defined function. This, of course,
> doesn't work - the parameter 'personId' isn't defined in the FROM clause.
> Any ideas on the best way to do this?
>
> Thanks,
> Phil
>|||I like it the way you didi it before Whats wrong with this for you ?
Like you mentioned a table function based on a column in the resultset
won=B4t work.
HTH, Jens Suessmeyer.|||As usual, I didn't give ALL of the information ;-)
I actually have several tables with join tables to People. Plus, the People
table has additional attributes - Names, PhoneNumbers, etc. I was trying to
centralize the 'GetPerson' logic, but it doesn't look like I'll be able to d
o
that easily.
"Jens" wrote:

> I like it the way you didi it before Whats wrong with this for you ?
> Like you mentioned a table function based on a column in the resultset
> won′t work.
> HTH, Jens Suessmeyer.
>