Print Page | Close Window

Delete after the output ref curosr

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10539
Printed Date: 27 Apr 2024 at 9:19am


Topic: Delete after the output ref curosr
Posted By: sruthi
Subject: Delete after the output ref curosr
Date Posted: 14 Jul 2010 at 11:28pm
Hi,
I am bad in english but i will try to explain my problem.
i am trying to connect procedure to CR.
my procedure should be designed in such a way that unlimited times by unlimited number of users can run the report and see the data on-demand.
They may refresh multiple times or run different reports multiple times and keep them open in their systems.
now my problem is
In procedure i am using reference cursor to throw output to report like
open cursor_name for
select x,y,z from a
if the same report is used by multiple users multiple times the data loded in final table 'a' is huge.
my point is can i delete the data after the output is thrown to report means can i use delete statement after the
open cursor_name for
select x,y,z from a
in procedure.
Thanks & Regards,
Sruthi


Replies:
Posted By: lockwelle
Date Posted: 19 Jul 2010 at 7:10am
in SQL Server, once a select is done, you don't need to keep the supporting tables...but you are using a cursor, which means you are looping through a collection.  In that case, I would assume you need to keep the table.
 
You could select a to be a temp table, then each instance of the stored proc would use its own copy of the data, and if you aren't looping, get rid of the cursor...actually, I would do everything in my power to get rid of the cursor, there are very few instances where you need a cursor...SQL WHILE loops are faster in 99% of the cases...and loops aren't needed that often either....dataset updates are faster still.
 
HTH


Print Page | Close Window