Calling a stored procedure in a SELECT statement

[neilkonitzer]

I'm not really sure if this can be done, but I'll give it a shot and see if someone's done this...

I am attempting to create a Select statement where one of the returned values is the output from an existing stored procedure. Here is an example of what I am trying to accomplish...

Select CatName, CatAge, CatColor, (exec sp_GetCatFood CatAge) As CatFood
From Animals

In the example above, the sp_GetCatFood stored procedure would take the record level value of CatAge as an input parameter and subsequentially return a value.

Unfortunately, using 'exec' in a select statement is not allowed.

I would prefer to keep the use of the stored procedure instead of a UDF to eliminate duplication of code maintenance.

Any ideas??? Thanks in advance!!

Neil Konitzer
Freisoft

http://www.freisoft.com

 

[nigelrivett]

Afraid you can't do that.

Can use openquery but that will create another connection.
Usual way is to create temp table and iterate through that.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ClaireHCM]

You can do it.But with User Defined Function.

UDF and Stored proc are different with each other in syntax.Check books on line and modify your stored procedure.By using UDF, you can passing the values in the whole column from a table as what u have in the select statement.

Good luck!

 

[nigelrivett]

From original question

>> I would prefer to keep the use of the stored procedure instead of a UDF to eliminate duplication of code maintenance.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.