MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 4th, 2004, 09:06 AM
Kadina Kadina is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2002
Location: Pennsylvania, USA
Posts: 129 Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 6 h 3 m 58 sec
Reputation Power: 12
Send a message via AIM to Kadina
Returning more than one result set from a stored procedure

Hi, I am wondering wether it is possible to return more than one result set to my application from a stored procedure. My sp is below, but all that returns to my application is the result of the first iteration of the invoice_cursor. Any help or alternate methods to achieve this would be appreciated.

Code:
CREATE PROCEDURE procBillingSummaryCastingDollars(
			@startDate Varchar(10),@endDate Varchar(10))  AS

DECLARE @invoice_id varchar(11);


DECLARE invoice_cursor CURSOR FOR 
SELECT invoice_id
FROM receivable_dist rd 
WHERE rd.POSTING_DATE BETWEEN @startDate AND @endDate 
AND (rd.GL_ACCOUNT_ID like '%3000%' OR rd.GL_ACCOUNT_ID like '%3101%') 
order by invoice_id

OPEN invoice_cursor

FETCH NEXT FROM invoice_cursor 
INTO @invoice_id

WHILE @@FETCH_STATUS = 0
BEGIN
   -- Declare an inner cursor based   
   -- on invoice_id from the outer cursor.
   IF charindex('C',@invoice_id) = 0 AND charindex('M',@invoice_id) = 0
   BEGIN
   	   SELECT ss.metal_code, ss.equip_code, ss.core,
	   CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars
	   FROM receivable r
	   INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id
	   INNER JOIN cust_order_line  cl ON cl.cust_order_id = rl.cust_order_id
	   INNER JOIN salesum_partid ss ON ss.partid = cl.part_id
	   WHERE rl.invoice_id = @invoice_id
   END
   ELSE
   BEGIN
	   SELECT ss.metal_code, ss.equip_code, ss.core, 
	   CASE r.status WHEN 'X' THEN rl.amount*-1 ELSE rl.amount END as CastingDollars
	   FROM receivable r
	   INNER JOIN receivable_line rl ON r.invoice_id = rl.invoice_id
	   INNER JOIN salesum_partid ss 
		ON substring(ltrim(rl.reference),1,(patindex('%-_-____%',ltrim(rl.reference))+6)) = ss.partid
	   WHERE r.invoice_id = @invoice_id
   END

   FETCH NEXT FROM invoice_cursor 
   INTO @invoice_id
END

CLOSE invoice_cursor
DEALLOCATE invoice_cursor
GO

Reply With Quote
  #2  
Old October 7th, 2004, 08:19 AM
Kadina Kadina is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2002
Location: Pennsylvania, USA
Posts: 129 Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level)Kadina User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 6 h 3 m 58 sec
Reputation Power: 12
Send a message via AIM to Kadina
For anyone interested, I solved this problem. The answer was to use myReader.NextResult() in my application (c#) to access the second result set.

Reply With Quote
  #3  
Old October 7th, 2004, 11:23 AM
shivanjali shivanjali is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2004
Posts: 11 shivanjali User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via AIM to shivanjali
i have similar problem.
I have just posted it today as new thread. Please let me know if you could think of a solution
thanks.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > Returning more than one result set from a stored procedure


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway