SQL2008: Stored procedure return 2 recordsets, while I need just one.

Dear experts

I have a stored procedure in SQL Server 2008 R2 , I post its code . My problem is that it return 3 recordsets while actually I need only the second. So my question is – how to avoid return cursor outside stored procedure?

 I have dificult to implement read second recordset and I want to save network with get back only needed data

 
ALTER PROCEDURE [dbo].[spGIA_GenInvoices]
 @Type TINYINT, --  0 - Get Info for proformas   1 - Get list with overdue proformas
 @UserID	int
 AS
 
 ---  some code ---
    
  IF @Type=0
  BEGIN
  
   SET @BDate=getdate(); SET @sRes='';SET @sErrInfo=''
   	
  	 DECLARE InCPP CURSOR  FOR 
	     SELECT dbo.LCampaignPlanPayments.LCampaignPlanPaymentID
	    FROM  dbo.LCampaigns INNER JOIN dbo.LCampaignPlanPayments ON dbo.LCampaigns.LCampaignID = dbo.LCampaignPlanPayments.LCampaignID
     	WHERE (dbo.LCampaignPlanPayments.LInvoiceID = 0) AND (dbo.LCampaigns.bActive = 1) AND (dbo.LCampaignPlanPayments.Date <= @BDate)

		OPEN InCPP
		FETCH NEXT FROM InCPP INTO @LCampaignPlanPaymentID
		WHILE (@@fetch_status =0)
		 BEGIN
		     SET @ID=0; SET @sErr =''
		 
		     BEGIN TRY
			   EXEC	@return_value = [dbo].spLPlanPaymentToProforma
					@ID = @LCampaignPlanPaymentID OUTPUT,
					@OurLCustomerID = @OurLCustomerID,
					@TypePay = @TypePay, 
					@UserID = @UserID 
					
					SET @sRes=@sRes + CAST(@LCampaignPlanPaymentID AS nvarchar)  + ','
					
			  END TRY
			  BEGIN CATCH
				  SET @HaveError=1;SET @sErr =  ERROR_MESSAGE() ;SET @sErrInfo=@sErrInfo + @sErr
			  END CATCH;  

		   FETCH NEXT FROM InCPP INTO @LCampaignPlanPaymentID
		  END
			
		CLOSE InCPP
		DEALLOCATE InCPP
  	
-- I Want to return ONLY this recordset -  	
   	SELECT @sRes AS Res,@sErrInfo AS ErrInfo 
  	RETURN
  END

Open in new window

Untitled.png
dvplayltdAsked:
Who is Participating?
 
Anthony PerkinsCommented:
You will have to modify the Stored Procedure spLPlanPaymentToProforma to pass in a flag to not display the recordset in the first case.

Sorry but there is no magic bullet.
0
 
PortletPaulfreelancerCommented:
"CLOSE cursorname" and then "DEALLOCATE cursorname".  Books OnLine under @@FETCH_STATUS has an example.

{+ edit sorry I see you have it in the code, oh well}
0
 
dvplayltdAuthor Commented:
I need at all CURSOR to not go outside the SP.  May be it is some CURSOR option, or additional code?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Brian CroweDatabase AdministratorCommented:
What is in these other recordsets?
0
 
dvplayltdAuthor Commented:
To BriCrowe

Look the pic I uploaded with primary post and SP code. I need just second one result set  the one with res and ErrInfo fields.
0
 
dvplayltdAuthor Commented:
10x. I already do this and solve the question. But I have many other cases like this and now I see they produced additional network comminications, so I was hope to have some unknow to me commands.
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.

All Courses

From novice to tech pro — start learning today.