Is there anything like DoEvents in a stored proc?

Hello,
I have a stored proc that does a series of things
1) create table if doesn't exist else delete all rows
2) select into this table
3) create another table if doesn't exist else delet all rows
4) select large number of records into this table
5) count records
6) delete records if more than a certain number of records
and I also print status in between each for debugging.
I am wondering if there is a way to have the stored procedure give up
some cpu time and also output the print statements before the entire
stored proc has finished.
Thanks
RandyIt seems that your procedure is written ineficiently...
prints are just data and get returned when you come to them.
Probbaly Waitfor Delay will do the job for you.
Bojidar Alexandrov
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>|||I've used PRINT statements for debugging large SPROCS - they do not come out
in the expected spot at all times.
I assume this is related to the way ADO from VISUAL BASIC has similar
issues - the PRINT statements go into the ERROR COLLECTION of the CONNECTION
OJBECT and seem to wait for RECORDSET processing - can't seem to get both.
In QUERY ANALYZER I've gotten around this issue by sometimes setting the
RESULT PANE to RETURN TEXT as opposed to DATA in a GRID - that way you get
one result window for both the RECORDSET data and the PRINT statements.
I've also at times changed all my PRINT statements to be SELECT statements
for debugging. And in reality if some of those "prints" remain in the
production code, it's better to have them as SELECT statements.
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>|||> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
I would question whether it makes sense to move data around in this
inefficient sequential manner. Can you create a view rather than copying
rows into another table? Inserting rows then counting and deleting them
seems especially pointless. Why can't you write your query (4) to insert
only the rows you require to start with?
If you need more help, please post DDL, sample data and show your required
result.
http://www.aspfaq.com/5006
David Portas
SQL Server MVP
--|||Thanks for the reply.
The reason I can't just select the records I need in the first place is
because I have to make sure that I have complete months worth of data.
So my logic is something like this.
1) Select top 104000 records into temp table
2) Select count if I have exactly 104000 records then I know that most
likely I have a partial months worth of data so now select the oldest
date, do some date math so that I have the date of the first day of the
next month then delete all records that are older than that.
It is actually even more complicated than that because I am building up
very specific data that could not be retrieved in a single query.
I know that it is not efficient, but this runs at night and so it is
more important to have the data accurate and the stored proc easy to
debug.
Thanks
Randy
In article <epadnUGk2aiLNwPdRVn-uQ@.giganews.com>,
REMOVE_BEFORE_REPLYING_dportas@.acm.org says...
> I would question whether it makes sense to move data around in this
> inefficient sequential manner. Can you create a view rather than copying
> rows into another table? Inserting rows then counting and deleting them
> seems especially pointless. Why can't you write your query (4) to insert
> only the rows you require to start with?
> If you need more help, please post DDL, sample data and show your required
> result.
> http://www.aspfaq.com/5006
>|||You want (at most) the top 104000 rows by date but only complete months? Try
this:
DECLARE @.dt DATETIME
SET ROWCOUNT 104001
SELECT @.dt = DATEADD(MONTH,DATEDIFF(MONTH,-1,xdate),0)
FROM SomeTable
ORDER BY xdate DESC
SET ROWCOUNT 0
SELECT *
FROM SomeTable
WHERE xdate >= @.dt
I'll be the first to admit that this is far from perfect. It uses a
proprietary, undocumented trick that isn't guaranteed to work in future
versions of SQLServer. I'm sure there are better methods of getting the
result you need but without a full understanding of your business
requirements this is just intended to illustrate one possibility.
David Portas
SQL Server MVP
--