Executing stored procedure in select query

Question

I'm using SQL Server 2008 R2 and I am trying to run a query where a stored procedure will also be executed.

The query is:

select a.custnmbr, a.custname, a.salsterr, b.itemnmbr, b.itemdesc, d.slprsnid ,exec dbo.QtySoldPerMonth a.custnmbr, b.itemnmbr, @year 
from rm00101_temp a, iv00101_temp b 
inner join sop30300_RPT c on b.itemnmbr = c.itemnmbr
inner join sop30200_RPT d on c.sopnumbe = d.sopnumbe
where
b.itemnmbr like @houseCode + '%' and itmclscd like @classCode + '%'
AND DATEPART(year, d.docdate) = @year
group by a.custnmbr, a.custname, a.salsterr, b.itemnmbr, b.itemdesc, d.slprsnid
order by d.slprsnid, b.itemnmbr 

What I'm really asking is how do I go about including the execution of the dbo.QtySoldPerMonth stored procedure in the select query? Also, the parameters for the stored procedures are: @custNo = a.custnmbr, @itemNo = b.itemnmbr and @year = @year.

Any help on how to rewrite the query to execute the sp will be appreciated.

Answer 1

1. create temp table for sp output
2. exec stored proc into temp table

3. join temp table to the rest of your query

   create table #temp(yourCol1 int, your Col2 int...);
   
   insert #temp(yourCol1,yourCol1...)
   exec dbo.QtySoldPerMonth 
   
   select * from blah
       join #temp t on (blah.blah=t.id...)
   

Answer 2

You can't incorporate SP results to query, but you can dump SP to a table. Example: http://blog.sqlauthority.com/2009/09/23/sql-server-insert-values-of-stored-procedure-in-table-use-table-valued-function/ But in your case it's not an option, as you want SP result with different parameters for every row; unless you modify SP so it returns a result set that you can later use after inserting it into table.

Answer 3

You can't execute a stored procedure as part of another query.

See if you can use a view of UDF to represent the same structure that the SP would return.

Edit

Another option: execute the stored procedure first and use the results in your main query.