Solved

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

Posted on 2013-05-30
6
324 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

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!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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