Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 10th, 2009, 01:40 PM
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Return Recordset from Stored Procedure?

I'm being told that you can't return a recordset from a stored procedure where you have procedural code. I have no experience with SQL Server at all but this is making my BS meter twitch wildly. Is this true for this version of SQL Server and newer versions?
 
Old March 10th, 2009, 01:49 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

It depends on what you mean by 'Recordset'. To me the results of:

sql Code:
SELECT * FROM <Table>

returns a Result set or record set; simply a collection of rows from the table. If, however, you are asking if SQL Server can return an ADO Recordset object (which you might use in a Classic ASP application) then no, I do not believe you can do that.


hth.
-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
 
Old March 10th, 2009, 02:54 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by WildRover View Post
I'm being told that you can't return a recordset from a stored procedure where you have procedural code. I have no experience with SQL Server at all but this is making my BS meter twitch wildly. Is this true for this version of SQL Server and newer versions?
The only way you can do it is if the procedureal code stuffs all of it's RBAR (Row By Agonizing Row) output into a table and then does a final select from the table. Haven't tried it with Temp tables and dunno if it'll work on those using something like OPENROWSET. If you do stuff the procedureal code into a temp table along with a final select, you can still do an INSERT/EXEC from the stored proc. Couple of other things you need to observe like having SET NOCOUNT ON in the proc, etc.
__________________
--Jeff Moden
 
Old March 11th, 2009, 06:21 AM
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So could you do something like this?...
***********************************************
Code:
SP_Name(arg1, arg2) as ADO.Recordset
 
rsTempTbl = OpenRecordset("Select * from TempTbl")
 
rs1 = OpenRecordset(Select * from tbl1 where x = arg1")
 
do until rs1.eof
   If rs1!field1 = "whatever" then
      rs2 = OpenRecordset("Select * from tbl2 where z = rs1!field4 and y = arg2")
      do until rs2.eof
         if rs1!field3 = "somthin" and rs2!fieldv = "sumpinelse" then
            rsTempTbl.add
            rsTempTbl!fielda = "avalue"
            rsTempTbl.update
         end if
         rs2.movenext
      loop
      rs2.close
   end if
   rs1.movenext
loop
rs1.close
rsTempTbl.close
 
/* Now return recordset from SP */
SP_Name = OpenRecordset("Select * from TempTbl")
 
end procedure
************************************************** ***
I'm sure this isn't very close to SQL Server SP syntax but hopefully you get my drift.
 
Old March 11th, 2009, 09:03 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Your first post asked about returning a recordset (an ado concept) from a stored procedure (a database concept). In your last post you have some code (VB?) but it doesn't seem to use any stored procs and has a function, OpenRecordset, that we can't see - as well as numerous errors etc. that make it hard to understand.
Can you clarify what you are trying to achieve and, if you post code, make sure it's acurate and relevant?
__________________
Joe
http://joe.fawcett.name/
 
Old March 11th, 2009, 09:09 AM
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by joefawcett View Post
Your first post asked about returning a recordset (an ado concept) from a stored procedure (a database concept). In your last post you have some code (VB?) but it doesn't seem to use any stored procs and has a function, OpenRecordset, that we can't see - as well as numerous errors etc. that make it hard to understand.
Can you clarify what you are trying to achieve and, if you post code, make sure it's acurate and relevant?
Joe, I'm trying to find out if it is possible to return a recordset from a SQL Server Stored Procedure (SP). I don't know SQL Server. I posted some pseudo code on what I thought might be possible in a SQL Server SP so people could get a feel for what I want the SP to do.
 
Old March 21st, 2009, 04:42 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by WildRover View Post
Joe, I'm trying to find out if it is possible to return a recordset from a SQL Server Stored Procedure (SP). I don't know SQL Server. I posted some pseudo code on what I thought might be possible in a SQL Server SP so people could get a feel for what I want the SP to do.
Yep... it is... even from a procedural SP that returns one bloody row at a time so, yeah, the BS meter you spoke of is there although it's a bit more of them not knowing how to do it rather than pure BS.

How many rows at a time does the stored procedure you're talking about return at a time? Just one or at least one (could be more). The reason I ask is so I know what kind of demo to put together for you.

And, my sincere apologies for not getting back to you on this earlier.
__________________
--Jeff Moden
 
Old March 23rd, 2009, 06:44 AM
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff, thanks much for the reply. What I'm looking to get back from the SP is a recordset. Could be zero records, could be 1 record, could be more. Probably no more than 10. I'd like to get all records back at once.

Maybe a recordset isn't the best thing to use. I'm calling the SP from VB6 using ADO and the recordset idea seemed easy to implement. Is there a better way?
 
Old March 23rd, 2009, 08:17 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by WildRover View Post
Jeff, thanks much for the reply. What I'm looking to get back from the SP is a recordset. Could be zero records, could be 1 record, could be more. Probably no more than 10. I'd like to get all records back at once.

Maybe a recordset isn't the best thing to use. I'm calling the SP from VB6 using ADO and the recordset idea seemed easy to implement. Is there a better way?
On my way to work... I'll take a crack at a demo for you tonight.
__________________
--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedure return value is NULL kshort ASP.NET 2.0 Basics 16 March 14th, 2017 12:45 PM
[Resolved] Get return value from stored procedure snufse ASP.NET 2.0 Basics 1 August 8th, 2008 11:05 AM
Stored Procedure return value dmiles C# 3 December 6th, 2007 05:20 AM
Can i return a recordset in a Stored procedure chiefouko VB Databases Basics 6 December 12th, 2006 03:34 AM
Can i return a recordset in a Stored procedure chiefouko SQL Server 2000 1 June 23rd, 2003 06:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.