Solved

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

Posted on 2013-05-30
6
326 Views
Last Modified: 2013-05-31
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
Comment
Question by:dvplayltd
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207123
"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
 

Author Comment

by:dvplayltd
ID: 39207179
I need at all CURSOR to not go outside the SP.  May be it is some CURSOR option, or additional code?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39207840
What is in these other recordsets?
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:dvplayltd
ID: 39208140
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39210445
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
 

Author Closing Comment

by:dvplayltd
ID: 39210856
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question