Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Return a Table from a Stored Procedure Expand / Collapse
Author
Message
Posted Friday, October 19, 2007 7:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, October 19, 2007 7:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Friday, October 19, 2007 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Friday, October 19, 2007 8:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, October 19, 2007 8:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Friday, October 19, 2007 8:18 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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
Posted Friday, October 19, 2007 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Friday, October 19, 2007 8:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:47 AM
Points: 967, 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
Post #412839
Posted Friday, October 19, 2007 9:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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
Posted Friday, October 19, 2007 10:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight 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 »

Add to briefcase 12»»

Permissions Expand / Collapse