| Arpan De 2002-11-09, 5:23 am |
| Consder the following stored procedure:
CREATE PROCEDURE Earnings AS SELECT adet.ErnCode
FROM
ADET AS adet,
ERNMST AS ern
WHERE
adet.ErnCode=ern.ErnCode AND
ern.ErnDed='E'
Suppose the above stored procedure produces 2 ErnCodes - HRA & CCA in the resultset. Using these 2 ErnCodes, I want to frame another stored procedure which should retrieve only the HRA & CCA records from another table ('coz the 1st procedure fetched HRA & CCA only as the ErnCodes). The rest of the records pertaining to the other ErnCodes should be completely neglected. Please note that in reality, I am not aware of the ErnCodes that are being generated by the above procedure. Had I been aware of the ErnCodes, I could have framed the 2nd stored procedure like this:
SELECT em.ECode,em.EName,
(SELECT Amt FROM ADET AS adet,EMST AS em WHERE ErnCode='CCA' AND em.ECode=adet.ECode)AS CCA,
(SELECT Amt FROM ADET AS adet,EMST AS em WHERE ErnCode='HRA' AND em.ECode=adet.ECode) AS HRA
FROM.................................
where I am using the 2 ErnCodes retrieved from the 1st procedure (as shown at the very top of this post) as one of the conditions in the WHERE clause & also as the alias column name in the 2nd stored procedure. But since in reality, I am not aware of the ErnCodes retrieved from the 1st stored procedure, how do I do frame the 2nd stored procedure i.e. how do I frame the 2nd procedure when one of the conditions in the WHERE clause & the alias column names are dynamic?
Thanks,
Arpan
|