I tried to search the forum for an answer to this, but got a timeout error, so I hope this hasn't been asked before. I was wondering if there's a way to call a stored procedure from a subquery?I'm working on a fairly large SP (around 200 lines so far) that returns 3 tables (individual, provincial, and national averages). Anyway, for most question types, I can easily calculate the number of responses to an answer, but in some instances (due to the initial DB design, which I didn't do), I can't. For the 'easy' case, I can use...SELECT t1.AnswerID, t1.Average, ( SELECT COUNT(*) FROM Assessments t3 WHERE t3.AnswerID = t1.AnswerID ) AS NumPatients FROM WebNationalAverages t1 INNER JOIN Answers t2 ON t2.ID = t1.AnswerID WHERE t1.QuestionID = @QuestionID ORDER BY t2.AnswerOrder
...However, some of the questions have an answer structure like...parentAnswerID1 ChildAnswerID1 (belongs to parentAnswerID1) ChildAnswerID2 (belongs to parentAnswerID1)parentAnswerID2 ChildAnswerID3 (belongs to parentAnswerID2) etc...
...The ParentAnswerIDs aren't stored in Assessments table (from above), only the Child answerIDs are. So, I have a SP that looks at the parentAnswerID and finds all of it's children to calculate the number of responses that belong to it (hope that makes sense).So for questions like this, I'd like to call the SP in place of the subquery. Is that posible? If so, how?
Calling a SP from a subquery?
Posted - 2005-08-10 : 11:53:18
