Fetch Next 1

[arpan]

A simple stored procedure retrieves, say, 2 records - CCA & HRA under a column named ErnCode. Please note that in reality, as a programmer, I am not aware of the ErnCodes that will be actually retrieved from the 1st stored procedure while I am coding the stored procedure. I have framed a 2nd stored procedure that will retrieve only the HRA & CCA records from another table (since the 1st procedure retrieved only HRA & CCA). What I am doing in the 2nd procedure is first creating a temporary table & populating the temp table with the records got from the 1st procedure & then creating a CURSOR & populating the cursor with the records in the temp table. This is the 2nd procedure which I have framed:

CREATE PROCEDURE PSlip
@month varchar(5),
@year varchar(5)
AS
DECLARE
@sql varchar(7000),
@erncode varchar(20),
@ernname varchar(200)
CREATE TABLE #TempTable
(ErnCode varchar(20),ErnName varchar(200))
INSERT INTO #TempTable
EXEC Earnings @ccode --this is the 1st stored procedure

DECLARE ErnCodeCursor CURSOR
FOR SELECT ErnCode,ErnName FROM #TempTable
OPEN ErnCodeCursor

FETCH NEXT FROM ErnCodeCursor INTO @erncode,@ernname
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @sql='SELECT DISTINCT(em.ECode),em.EName,attd.Basic,
(SELECT Amt FROM QL01ADET AS adet WHERE adet.ErnCode="' + @erncode + '" AND em.ECode=adet.ECode AND adet.Type IS NULL) AS "' + @ernname + '", FROM..................'
FETCH NEXT FROM ErnCodeCursor INTO @erncode,@ernname
END
SET @sql=REPLACE(@sql,", FROM"," FROM&quot
EXEC (@sql)
CLOSE ErnCodeCursor
DEALLOCATE ErnCodeCursor

Please note that the reason I am using the REPLACE function is because there will be an extra comma(,) just before the last FROM in the SQL query. To omit that comma, I am making use of the REPLACE function.

Now when I execute the procedure in the Query Analyzer using

EXEC PSlip 4,2001

only the CCA records get retrieved. The HRA records are not at all retrieved. Why is this happening? Shouldn't the FETCH NEXT FROM ErnCodeCursor then go to the HRA row so that the stored procedure can retrieve the HRA records? Where am I going wrong? What do I do to ensure that all the records from QL01ADET & QL_EMST tables get retrieved that correspond to the ErnCodes that have been populated in the ErnCodeCursor when the 2nd stored procedure is executed?

One again please note that in reality, as a programmer, I am not aware of the ErnCodes that will be actually retrieved from the 1st stored procedure while I am coding both the stored procedure.

Thanks,

Arpan

 

[MeanGreen]

Since I can't see your entire Cursor that builds your query, am not sure what you have build. What you need to do is display the @sql variable before it executes after the cursor.
Since you have a temporay table with your values, have you considered using a JOIN instead of a cursor? CURSOR's are slow and usually you can use the JOIN to get what you are trying to get, I think.
Please post your @sql string, and maybe the entire cursor so we can look to see what you are trying to create. I will try to provide an accurate solution for you if you can do that.

Hope this helps.

 

[arpan]

Hello,

Thanks for your response. You are absolutely right in saying that I can do the same thing using a JOIN instead of using a CURSOR. Actually, I am fairly new to cursors; that's the reason why I am trying to do this using cursors. Of course, as you have said, using JOINS will be more effecient.

Now as far as the 'entire' cursor is concerned, please note that whatever cursor code you can see in the stored procedure, that is the entire cursor. I haven't coded anything extra for the cursor. Let me tell you the biggest problem I am encountering. As I have already mentioned in my post, I just don't have any idea on what could be the ErnCodes when I am coding both the procedures i.e. the ErnCodes generated by the 1st stored procedure could be just about anything - ABCD, XYZ, GYWK, HFDS34, IOP9 - just about anything. Also I want that the 2nd stored procedure should only retrieve those ErnCode records which correspond to the ErnCodes in the cursor (or the temp table as well). For eg. if I 1st stored procedure generates CCA & HRA as the ErnCodes, these 2 ErnCodes will be there in the cursor as well as in the temp table & the 2nd stored procedure should retrieve only the CCA records & the HRA records. Had the 1st stored procedure generated CCA, HRA & PF as the ErnCodes, the 2nd procedure should fetch the CCA records, HRA records & the PF records. The table from where these records will be retrieved will also have other ErnCode records but those have to be neglected since the 1st procedure generated only CCA, HRA & PF ErnCodes. Had I been aware that the 1st stored procedure would generate CCA & HRA ErnCodes, then there wouldn't have been any problems in framing the 2nd stored procedure. Also cursors could have been avoided. It could have been as simple as this:

CREATE PROCEDURE PSlip
AS DECLARE @sql varchar(6000)
SET @sql='SELECT DISTINCT(em.ECode),em.EName,attd.Basic,
(SELECT Amt FROM QL01ADET AS adet WHERE adet.ErnCode="CCA" AND em.ECode=adet.ECode AND adet.Type IS NULL) AS "CCA",
(SELECT Amt FROM QL01ADET AS adet WHERE adet.ErnCode="HRA" AND em.ECode=adet.ECode AND adet.Type IS NULL) AS "HRA"
FROM..................'

As you can see, one of the conditions in the WHERE clause as well as the alias column names are CCA & HRA respectivly (since the 1st stored procedure generated CCA & HRA as the ErnCodes) but as I have already said, I AM NOT AWARE OF THE ERNCODES THAT WILL BE GENERATED BY THE FIRST STORED PROCEDURE WHILE I AM CODING THE STORED PROCEDURE. The CCA & HRA ErnCodes I have shown are mere examples. So how do I code the condition & the alias column name since both of them depend on what ErnCodes the 1st stored procedure generates i.e. they are dynamic.

I have posted this question in many newsgroups & forums but unfortunately, I guess, nobody is understanding the fact that I just don't have any idea on what the ErnCodes could be when I am coding the stored procedures since the ErnCodes are being generated dynamically by the 1st stored procedure. I hope you have understood what I am trying to achieve.

So under such scenario, how do I frame the 2nd stored procedure?

Regards,

Arpan

 

[MeanGreen]

I think you just need to move some stuff around. The DYNAMIC part is inside the loop, then the final FROM is appended after you are done looping. Try this:


CREATE PROCEDURE PSlip
@month varchar(5),
@year varchar(5)
AS
DECLARE
@sql varchar(7000),
@erncode varchar(20),
@ernname varchar(200)
CREATE TABLE #TempTable
(ErnCode varchar(20),ErnName varchar(200))
INSERT INTO #TempTable
EXEC Earnings @ccode --this is the 1st stored procedure

DECLARE ErnCodeCursor CURSOR
FOR SELECT ErnCode,ErnName FROM #TempTable
OPEN ErnCodeCursor

SET @sql='SELECT DISTINCT(em.ECode),em.EName,attd.Basic'

FETCH NEXT FROM ErnCodeCursor INTO @erncode,@ernname
WHILE(@@FETCH_STATUS=0)
BEGIN
select @sql = @sql + ',(SELECT Amt FROM QL01ADET AS adet WHERE adet.ErnCode=''' + @erncode + ''' AND em.ECode=adet.ECode AND adet.Type IS NULL) AS ''' + @ernname + ''''

FETCH NEXT FROM ErnCodeCursor INTO @erncode,@ernname
END
select @sql = @sql + 'FROM ........ '
EXEC (@sql)
CLOSE ErnCodeCursor
DEALLOCATE ErnCodeCursor


Hope this helps.

 

[arpan]

Hello My Dear Friend,

If anytime I am given the opportuniity to rate an answer with 100 stars, I would definitely rate your answer with 100 stars, that too, without thinking twice. Your suggestion has indeed helped me a lot & a lot & a lot. And I really mean it. You have, at last, provided me a concrete solution for my problem & have taken me out of this mess. I am highly obliged to you. Thank you very very very much for the same.

Regards,

Arpan