[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

[si_the_geek]

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

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:

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

[aconybeare]

Si,

Cunning stunt, thanks a lot!

Cheers Al