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
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2295 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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2295 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10578 Visits: 11966
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
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1321 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
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 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
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: 1207 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