Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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
0
dvplayltd
Asked:
dvplayltd
1 Solution
 
PortletPaulCommented:
"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
 
Brian CroweCommented:
What is in these other recordsets?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now