Using The Records Retrieved From One Stored Procedure in Another Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using The Records Retrieved From One Stored Procedure in Another Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-11 : 10:37:03

Arpan writes "Consider the following stored procedure:

CREATE PROCEDURE Earnings
@ccode varchar(10)
AS
EXECUTE('SELECT DISTINCT(adet.ErnCode),ern.ErnName FROM ' + @ccode + '01ADET AS adet,ERNMST AS ern WHERE ern.ErnCode=adet.ErnCode AND ern.ErnDed="E"')

Assume that the ErnCodes retrieved from the above procedure are HRA, CCA, PF & PT. Now I want to retrieve records from QL01ADET & another table named QL_EMST but only those records should be retrieved in which ErnCode is HRA or CCA or PF or PT. The rest of the records should be neglected. I want to display the records in the same way as this SELECT query would retrieve the records:

SELECT em.ECode,em.EName,
(SELECT TOP 1 Amt FROM QL01ADET AS adet,QL_EMST AS em WHERE em.ECode=adet.ECode AND adet.ErnCode='PF') AS PF,
(SELECT TOP 1 Amt FROM QL01ADET AS adet,QL_EMST AS em WHERE em.ECode=adet.ECode AND adet.ErnCode='PT') AS PT,
(SELECT TOP 1 Amt FROM QL01ADET AS adet,QL_EMST AS em WHERE em.ECode=adet.ECode AND adet.ErnCode='HRA') AS HRA,
(SELECT TOP 1 Amt FROM QL01ADET AS adet,QL_EMST AS em WHERE em.ECode=adet.ECode AND adet.ErnCode='CCA') AS CCA
FROM.....................

i.e. I want that HRA, CCA, PF & PT to be the column names though they are the records under the ErnCode column in the table named QL01ADET. If the above SELECT query is executed in the Query Analyzer, the records will be displayed as follows:


------------------------------------------------------------
ECode     EName         PF       PT       HRA       CCA
------------------------------------------------------------
  1      Mr. John       400      200      250       350
  2      Mr. Ian        100      150      200       250
  3      Mrs. Anna      500      300      400       600
------------------------------------------------------------

Now I want to create a stored procedure that will display the records in the same way as I have shown above. How do I create the stored procedure? Please note that in reality, I am not aware of the different ErnCodes that exist in the tables QL01ADET & ERNMST while coding. Not only the alias column names HRA, CCA, PF & PT should be generated dynamically but also adet.ErnCode='XXXX' (which is one of the conditions given in the WHERE clause) should be generated dynamically where XXXX is the ErnCode. This is because if tomorrow if a new ErnCode, say, ADSAL, is inserted in the tables ERNMST & QL01ADET, the records in QL01ADET corresponding to this new ErnCode ADSAL should also be displayed.

Thanks,

Arpan"

SMerrill
Posting Yak Master

206 Posts

Posted - 2002-11-13 : 18:10:59

I typically use a combination of IIF and SUM() to get the results on a crosstab idea like this. For example, consider this pseudocode:

SELECT ECode, EFName,
  SUM(IIF(adet.ernCode='PF',Amt,0)) as PF,
  SUM(IIF(adet.ernCode='PT',Amt,0)) as PT,
  SUM(IIF(adet.ernCode='HRA',Amt,0)) as HRA,
  SUM(IIF(adet.ernCode='CCA',Amt,0)) as CCA ...

This consolidates the information nicely. I hope this is enough to give you the inspiration you need.

--SMerrill
Seattle, WA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources