SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return a Table from a Stored Procedure


Return a Table from a Stored Procedure

Author
Message
goodguy
goodguy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1037
I am an application programmer using VB6 with SQL Server 2000 as a backend and Crystal Reports 8 as my reporting tool.

Thanks to this site, I can now write stored procedures and invoke them from my app code. But I have yet to master returning a resultset as a table variable to VB6 and CR8.

Currently my sprocs delete all past records in the actual target table before inserting the current ones. Since my app is distributed, I am wondering what will happen when two users invoke the same report simultaneously with different criteria. I've decided it'd be better to switch to table variables instead.

Can anyone help?


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5663 Visits: 1585
Hi,

you could use both table variables and temporary tables. In terms of scope both of them will be restricted to your stored procedures. Both have advantages and disadvantages. Basically if you have very large tables, use temptables.

very simple examples for thes:
declare @table table(a int, b int)
insert into @table values (1,2)
select * from @table

create table #table (a int, b int)
insert into #table values (1,2)
select * from #table

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Conway Painting
Conway Painting
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 7
Hi

You would generally return the results in a RecordSet.

Set RS=Server.CreateObject("Adodb.Recprdset")
RS.ActiveConnection="Some connection to a database"
Sql = "Select Id, Name From Users Where Name like '%Smi%'
Set RS=RS.Open SQL

If Not RS.Eof Then
While Not Rs.Eof
Response.Write("Id =" & RS("Id") & " - ")
Response.Write("Name =" & RS("Name") & " ")
RS.MoveNext
WEND
End If

Hope it helps
goodguy
goodguy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1037
Thanks, pal, but what I meant was how do I retrieve the temp table returned by the sproc into my vb code? How do I get a handle on it via ADO?


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5663 Visits: 1585
Conway Painting is of course right, that you would generally return the data in the result sets. The temptable and table variables in this case are most useful for producing the result set inside the stored procedure.

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26422 Visits: 12488
goodguy (10/19/2007)
Thanks, pal, but what I meant was how do I retrieve the temp table returned by the sproc into my vb code? How do I get a handle on it via ADO?

Actually people use to loop through returned recordset.
goodguy
goodguy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1037
Set RS=Server.CreateObject("Adodb.Recprdset")
RS.ActiveConnection="Some connection to a database"
Sql = "Select Id, Name From Users Where Name like '%Smi%'
Set RS=RS.Open SQL

If Not RS.Eof Then
While Not Rs.Eof
Response.Write("Id =" & RS("Id") & " - ")
Response.Write("Name =" & RS("Name") & " ")
RS.MoveNext
WEND
End If


I am afraid I haven't been lucid enough: I need a recordset returned in VB6 by my sproc. How do I do that?


Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Prasad Bhogadi
Prasad Bhogadi
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2563 Visits: 450
Your frontend (Visual Basic) would not really know whethere you have used a temp table or if you are fetching data from the original table. Its all inside your stored procedure. All that your stored procedure returns is a result set and you would use the base syntax of
IF NOT RecordSet.BOF AND RecordSet.EOF
Do logic like Recordset.movenext , Recordset.moveprevious etc
----
or you can bind to the grid using
.Datasource property

Prasad Bhogadi
www.inforaise.com
mrpolecat
mrpolecat
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 856
We could help more if we saw your proc but let say right now your proc creates a table by doing

select a,b,c into temptable from permtable

and then your app gets it's data via and ado select from temptable


if you change your proc to just
select a,b,c from permtable

then it will return the recordset a,b,c

in your app you get the results into your recordset by

rs = conn.execute(proc)






David McFarland
David McFarland
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3045 Visits: 214
In addition to what mrpolecat stated, a far more typical option in your environment would be to actually let the report use the resultset from the stored proc, instead of the app. I don't use Crystal anymore, but I'm pretty sure it's been capable of using a stored proc as a datasource for a VERY long time.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search