Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Select with results from stored procedure as additional column

Don't know if this is possible

I have a current select which returns 2 columns Specialty and StateList

I want to add the results of a stored procedure as a column for each row.

In essence...execute the stored proc on each row based on the the columns as paramaters

the stored procedure returns one row of data and one column only
In my example below I want the RESULTS of the stored procedured that's executed.

So with this logic
Specialty      StateList         resultsSPColumn
BMET            AL,FL               exec myproc @spec=Specialty, @sl=StateList
CCU              GA,WY             exec myproc @spec=Specialty, @sl=StateList

I would get this
Specialty      StateList         resultsSPColumn
BMET            AL,FL               220
CCU              GA,WY             10250
Avatar of awking00
awking00
Flag of United States of America image

If your stored procedure always returns one value for a given specialty and statelist, why not turn it into a function instead, then -
select specialty, statelist, myfunc(specialty, statelist) as resultscolumn from yourtable
You can store the result of stored procedure temporarily in a table variable and select from that but it's better to create a table function instead of stored procedure if you want to use it in your select statement.
Avatar of Larry Brister

ASKER

pourfard
Got an example of that?
Something similar to the following:
create function myfunc(@spec varchar, @list varchar)
returns int
as
begin
 declare @results int
 set @results = myproc(@spec,@list)
 return @results
end
ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yeah...figured
Isn't that kind of what I suggested? :-(
awking00

Didn't look closely at all answers when I posted points.

My bad
no harm, no foul :-)
@awking00 your suggestion will not work.


you wrote:  
set @results = myproc(@spec,@list)
which is not correct. myproc is a stored procedure and you need to execute it as the following:
EXEC @result = dbo.myproc @spec, @sl
I also said "something similar to" since I am not totally familiar with T-SQL syntax.
So where you have -
BEGIN
        DECLARE @Result INT
        ...  ==> This would be replaced by this EXEC @result = dbo.myproc @spec, @sl
        RETURN @Result
    END

You have to admit I was kind of close :-)