October 19, 2007 at 7:44 am
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?
October 19, 2007 at 7:50 am
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
October 19, 2007 at 8:10 am
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
October 19, 2007 at 8:16 am
October 19, 2007 at 8:17 am
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
October 19, 2007 at 8:18 am
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.
_____________
Code for TallyGenerator
October 19, 2007 at 8:39 am
[Quote]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[/Quote]
I am afraid I haven't been lucid enough: I need a recordset returned in VB6 by my sproc. How do I do that?
October 19, 2007 at 8:43 am
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
October 19, 2007 at 9:12 am
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)
October 19, 2007 at 10:22 am
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.
October 20, 2007 at 12:58 am
One of my sprocs that populates a DB table:
CREATE PROCEDURE [dbo].[sp_DAILYMOVEMENTS] @MyDate DateTime AS
DELETE MOVEMENT
INSERT INTO MOVEMENT(TODAY, ROOMNAME, ROLLOVER, ARRIVAL, DEPARTURE)
SELECT @MyDate, Description,
MAX(CASE WHEN checkindate @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover,
MAX(CASE WHEN checkindate = @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival,
MAX(CASE WHEN checkoutdate = @Mydate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure
FROM RESERVATIONROOM
GROUP BY Description
ORDER BY Description
GO
How can I modify it to return the table virtually?
October 20, 2007 at 10:34 am
CREATE PROCEDURE [dbo].[sp_DAILYMOVEMENTS] @MyDate DateTime AS
SELECT @MyDate, Description,
MAX(CASE WHEN checkindate @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover,
MAX(CASE WHEN checkindate = @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival,
MAX(CASE WHEN checkoutdate = @Mydate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure
FROM RESERVATIONROOM
GROUP BY Description
ORDER BY Description
October 21, 2007 at 6:37 am
October 22, 2007 at 12:07 am
mrpolecat's suggestion doesn't actually do the DELETE or INSERTs. If you want the one proc to do those and also return the newly inserted records from MOVEMENT then do something like this:
AS
DECLARE @ErrorCount int
DECLARE @MovementTemp TABLE
(TODAY datetime,
ROOMNAME varchar(256),
ROLLOVER varchar(20),
ARRIVAL varchar(20),
DEPARTURE varchar(20))
SELECT @ErrorCount = 0
INSERT INTO @MovementTemp (TODAY, ROOMNAME, ROLLOVER, ARRIVAL, DEPARTURE)
SELECT @MyDate, Description,
MAX(CASE WHEN checkindate @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover,
MAX(CASE WHEN checkindate = @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival,
MAX(CASE WHEN checkoutdate = @Mydate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure
FROM RESERVATIONROOM
GROUP BY Description
BEGIN TRANSACTION
DELETE FROM MOVEMENT
IF @@ERROR <> 0
SELECT @ErrorCount = @ErrorCount + 1
IF @ErrorCount = 0
BEGIN
INSERT INTO MOVEMENT (TODAY, ROOMNAME, ROLLOVER, ARRIVAL, DEPARTURE)
SELECT (TODAY, ROOMNAME, ROLLOVER, ARRIVAL, DEPARTURE)
FROM @MovementTemp
IF @@ERROR <> 0
SELECT @ErrorCount = @ErrorCount + 1
END
IF @ErrorCount = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
SELECT (TODAY, ROOMNAME, ROLLOVER, ARRIVAL, DEPARTURE)
FROM @MovementTemp
ORDER BY Description
This assumes SQL2000 for the table variable - if 7.0 then use a temp table instead. Note the transaction to ensure that you don't end up with an empty MOVEMENT table if you do something like violate a constraint during your INSERT. Not sure how many rows you have in your tables - if a lot you may want to consider a TRUNCATE instead of DELETE, depending on your recovery point objectives (do you need the row deletes logged?)
and the presence of any IDENTITY columns in MOVEMENT.
You may also want to rethink the whole delete rows approach - if this is a multiuser app you will want some sort of concurrency mechanism in place to cope with overlapping runs of this proc. As it stands above you get last-writer-wins, which may not be what you're after.
Regards,
Jacob
goodguy (10/20/2007)
One of my sprocs that populates a DB table:CREATE PROCEDURE [dbo].[sp_DAILYMOVEMENTS] @MyDate DateTime AS
DELETE MOVEMENT
INSERT INTO MOVEMENT(TODAY, ROOMNAME, ROLLOVER, ARRIVAL, DEPARTURE)
SELECT @MyDate, Description,
MAX(CASE WHEN checkindate @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover,
MAX(CASE WHEN checkindate = @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival,
MAX(CASE WHEN checkoutdate = @Mydate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure
FROM RESERVATIONROOM
GROUP BY Description
ORDER BY Description
GO
How can I modify it to return the table virtually?
October 22, 2007 at 12:58 am
Thanks, Luebbers, you've all been a great help.
I don't think I need to use the perm tables to hold my temp data anymore if I can pass the whole recordset to VB6 and CR8 by code, so no more inserts/deletes. A beautiful solution.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply