Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Call a SPROC from within a Select Query

I have a select statement that I need to call a SPROC and return a value as one of the values

I have a SPROC called DB_ext.dbo.uspCalcAvgLeadTime that I need to pass a product Item Number (iv2.itemnmbr)

Below is the statement I'm trying to use, what am I doing wrong?

Thanks as always

select iv2.itemnmbr
      ,iv1.itemdesc
      ,iv2.ORDRPNTQTY
      , exec DB_ext.dbo.uspCalcAvgLeadTime(iv2.itemnmbr) as CalcAverageLeadTime
      ,iv3.planningleadtime
      , iv1.itemtype
from iv00101 iv1
join iv00102 iv2 on iv2.itemnmbr=iv1.itemnmbr
      and iv2.locncode='LOCAL'
left join iv00103 iv3 on iv3.itemnmbr=iv1.itemnmbr
      and iv3.vendorid=iv2.primvndr
where iv2.ORDRPNTQTY>0
      and iv1.itemtype<>'2'
0
jdr0606
Asked:
jdr0606
1 Solution
 
Koen Van WielinkIT ConsultantCommented:
Stored procedures cannot be called from within a select statement. If you have to use a stored procedure, you have to call it separately, from within a cursor for example. The idea would be to store your normal select values in a temp table with an empty column for the value returned by the stored procedure. After that, you run a cursor over your temp table and update each row with the value returned by the stored procedure. This is not very efficient however.
You might want to consider changing your procedure to a function instead. Functions can be put inside a select clause.
0
 
HuaMinChenBusiness AnalystCommented:
One SP cannot be called within Select statement, or you should change the SP into a function and retry.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now