Solved

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

Posted on 2013-05-30
6
327 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 35
SQL Rewrite without the NULLIF 4 25
How to add a suffix to a value in a column based on the value in another column 4 30
Many to one in one row 2 35
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 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