[RESOLVED] SQL Server '05: Execute and return value from SP within select statement

[aconybeare]

Hi,

I'm wondering is it's possible to execute a stored proc from within a select statement, here are some query snippets which hopefully show clearly what I'm trying to do -

-- Current working example
SELECT p.User_Id
  ,p.First_Name
  ,p.Last_Name
  ,(SELECT ph.Detail FROM Phone ph WHERE ph.User_Id=p.User_Id AND ph.Phone_Type='Email' AND Status='A' AND Default_Opt=1) As Email
  ,(SELECT ph.Detail FROM Phone ph WHERE ph.User_Id=p.User_Id AND ph.Phone_Type='Phone' AND Status='A' AND Default_Opt=1) As Phone
FROM People p
WHERE p.Deleted=0


-- Concept\Idea (Attempt reduce code repetition)
SELECT p.User_Id
  ,p.First_Name
  ,p.Last_Name
  ,(EXEC up_gGetPersonPhone @Detail OUTPUT, 'Email', p.User_Id) As Email -- not sure how to get the output param
  ,(EXEC up_gGetPersonPhone @Detail OUTPUT, 'Phone', p.User_Id) As Phone -- not sure how to get the output param
FROM People p
WHERE p.Deleted=0

Any thoughts or ideas on how to achieve this will be greatly appreciated

Cheer Al