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

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 -

Code:

---

-- 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

It is possible if you use a User Defined Function instead of a Stored Procedure, eg:

Code:

---

CREATE FUNCTION  uf_gGetPersonPhone
(@Phone_Type AS VarChar,
 @User_Id AS VarChar
)
RETURNS VarChar
AS
BEGIN
        RETURN  SELECT ph.Detail FROM Phone ph WHERE ph.User_Id=@User_Id AND ph.Phone_Type=@Phone_Type AND Status='A' AND Default_Opt=1

END
;

---

(untested!)

Example usage:

Code:

---

SELECT p.User_Id
  ,p.First_Name
  ,p.Last_Name
  ,uf_gGetPersonPhone('Email', p.User_Id) As Email
  ,uf_gGetPersonPhone('Phone', p.User_Id) As Phone
FROM People p
...

---

Si,

Cunning stunt, thanks a lot!

Cheers Al