What's the best way to pass info from a temp table to VB?

[tanman1975]

I have temporary table created within a stored procedure that I'd like to pass back to a program written in VB 6.0

I know that you could pass cursors out of a stored procedure, but then how would you handle it on the VB side via a DataEnvironment command?

earlier in the program I was able to connect an ADODC control to a stored procedure where the stored procedure only contained a select statement. That worked fine. but when I tried to do the same thing in a stored procedure where data was being massaged in a temp table before the Select statement, of course, it failed. any ideas would be appreciated. Thank you.

 

[tlbroadbent]

After you've created the temp table and "massaged" the data, you just need to perform a select statement as the last action of the stored procedure. Make sure you add SET NOCOUNT ON at the beginning of the SP to avaoid multiple record sets consisting of messsages like "10 row(s) affected."

Example:

Create Procedure Myproc As

SET NOCOUNT ON

Create table #temp (<col definitions>)

Insert Into #temp
Select col1, col2, col3 From SomeTable

<massage the data>

Select * From #temp
Go Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tanman1975]

SET NOCOUNT ON is the command that does that? I've been banging my head all day trying to figure that out.

That's awesome. Thanks! I will name my firstborn after you

-Anuj

 

[Adam95]

At the end of the stored procedure could you SELECT * FROM #temp and hook a dataconnection to the storedprocedure?


Adam

 

[tanman1975]

adam,

yes that works as long as you use &quot;set nocount on&quot; at the beginning of your stored procedure