• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 37
  • Last Modified:

Return value from stored procedure inside a select statment

Select statement with value from stored procedure.

We have product table where we return all product info and a stored procedure that return the correct price for the product. (PHP)

The problem is when we want to sort on the price returned from SP.

Any suggestion?
0
myhrvold
Asked:
myhrvold
1 Solution
 
John TsioumprisSoftware & Systems EngineerCommented:
Well you could create a temp table  (from the SP) and join that to the product table
0
 
myhrvoldAuthor Commented:
The SP return only one value the price for the product.

SELECT WEB_Prod.ProdNo ,WEB_Prod.Descr FROM [WEB_Prod]

For each row, we run SP_GetCustomerProductPrice 'PRODNO','CUSTNO'

The stored procedure is very complex, over 1000 lines.....
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Well you could "enclose " the SP inside a While loop and feed the temp table.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
myhrvoldAuthor Commented:
Do you have any example code to pin me in right direction?
0
 
John TsioumprisSoftware & Systems EngineerCommented:
Take a look at Ms article
If still have issues let me know have to dig up my old sql files.
0
 
Scott PletcherSenior DBACommented:
The stored procedure is very complex, over 1000 lines.....

Yikes.  But you still need to convert the logic into an inline table-valued function if at all possible.  Then you can just CROSS APPLY to it, with great performance and its results become just another table in the query: you can sort on it, use WHERE on it, join on it, etc..
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You can modify the stored procedure, either to accept the list of ProdNo as input or you can join the  [WEB_Prod] table inside the stored procedure.
Between its not a good practice to name the storedprocedures with the prefix 'sp_'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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