Return a Table from a Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Return a Table from a Stored Procedure

19 posts, Page 1 of 2

1 2 »»

Return a Table from a Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

goodguy

Posted Friday, October 19, 2007 7:44 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 01, 2012 3:30 PM
Points: 292, Visits: 1,028

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

Post #412795

Andras Belokosztolszki

Posted Friday, October 19, 2007 7:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

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

Post #412801

Conway Painting

Posted Friday, October 19, 2007 8:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 25, 2008 4:27 AM
Points: 39, 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

Post #412816

goodguy

Posted Friday, October 19, 2007 8:16 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 01, 2012 3:30 PM
Points: 292, Visits: 1,028

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

Post #412822

Andras Belokosztolszki

Posted Friday, October 19, 2007 8:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

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

Post #412825

Sergiy

Posted Friday, October 19, 2007 8:18 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 4,557, Visits: 8,236

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.

Post #412826

goodguy

Posted Friday, October 19, 2007 8:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 01, 2012 3:30 PM
Points: 292, Visits: 1,028

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

Post #412835

Prasad Bhogadi

Posted Friday, October 19, 2007 8:43 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 02, 2013 5:00 AM
Points: 967, Visits: 447

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

Post #412839

mrpolecat

Posted Friday, October 19, 2007 9:12 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:10 AM
Points: 445, Visits: 840

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)

Post #412860

David McFarland

Posted Friday, October 19, 2007 10:22 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, December 08, 2008 8:42 AM
Points: 775, 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.

Post #412903

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions