How can I show results while WHILE is running
-
I am using a WHILE command and would like to have the results returned while the script is still running.
Here's the script with fields changed:
USE mydatabase
SET NOCOUNT ON
GO
DECLARE
@a INT
, @b-2 INT
SET @a = 1
SET @b-2 = 0
WHILE @a < 3
BEGIN
SELECT
CONVERT(CHAR(6), GETDATE()-@a, 0) AS 'EventDate'
, COUNT(eventname) as 'Amount of Events'
FROM
EVENTS
WHERE
(eventdate >= CONVERT(CHAR(8), GETDATE()-@a, 1) + ' 12:00:00'
AND eventdate >= CONVERT(CHAR(8), GETDATE()-@b, 1) + ' 12:00:00')
AND eventname = 'concert'
END
That just keeps running until it gathers ALL the data and then it displays everything. I want the results shown before it increments the variables. In other words, when it finishes one day, I want the results shown and then it go to the next day.
(BTW- in another thread I was shown a more efficient way to do the date/time, I'm working on making that change already).
-SQLBill
-
You may try to add a 'print' statement.
USE mydatabase
SET NOCOUNT ON
GO
declare @eventdate varchar(6), @amount int
SET @a = 1
SET @b-2 = 0
WHILE @a < 3
BEGIN
SELECT @eventdate = CONVERT(CHAR(6), GETDATE()-@a, 0), @amount = COUNT(eventname)
FROM EVENTS
WHERE (eventdate >= CONVERT(CHAR(8), GETDATE()-@a, 1) + ' 12:00:00'
AND eventdate >= CONVERT(CHAR(8), GETDATE()-@b, 1) + ' 12:00:00')
AND eventname = 'concert'
print @eventdate + ' ' + convert(vrachar(6), @amount)
END
-
Thanks Allen. I had tried the PRINT command but couldn't get it working correctly.
Should have thought about converting the int to varchar. Oh well...if we could think of everything there wouldn't be a need for this site.
Thanks for the help.
-SQLBill
-
To add to this question:
I am querying data from a large amount of data. I want the number of events for each day for a month's time. My query (see above) will actually have the WHILE statement as
WHILE @a < 32
Which would be quicker, more efficient:
1. Running the query against the actual data?
2. Creating a TEMP Table ##mydata with the data (EventDate and EventName) for the whole time period and then running the query against that temp table?
-SQLBill
-
Allen,
OOOPPPS. After running the query again and watching closely, the results are NOT being returned (PRINTed) until the WHILE exits. So, I'm beginning to think that when a WHILE is used nothing can be returned until the WHILE has been met.
-SQLBill
-
The print result will appear in message window. I know it is not perfect.
-
Another try at a solution!
Declare @start DateTime,@end DateTime, @month DateTime
Set @month='2 jan 2002'
Set @start= Convert(Char(8),@Month,120)+'1'
Set @end= DateAdd(ms,-3,DateAdd(m,1,@start))
Select Convert(char(11),EventDate,106),Count(*)
From MyTable
Where EventDate Between @start and @end
Group by Convert(char(11),EventDate,106)
Order by Convert(char(11),EventDate,106)
Try and add a replicate(' ',4096) after your COUNT(eventname) as 'Amount of Events'
Indexes on Eventdate/Eventname
-
Allen,
Yep, I know they will appear in the Message window. That's what I want to happen. But they all appear at once, not as they occur in the WHILE statement.
I ran it for one day, the results showed up in 4 minutes. I ran it for 10 days and never saw a result until it finished 30 minutes later.
-SQLBill
-
Hi SQLBill,
not sure if this really helps but I've found the following on http://www.umachandar.com/
--To store the progress details
CREATE TABLE ##Progress( Step int , ItTookSoLong int )
GO
CREATE PROC EverRunningProc
AS
DECLARE @step int , @Delay char(10)
SELECT @step = 1
WHILE( 1 = 1)
BEGIN
IF @step = 1000 BREAK
INSERT ##Progress VALUES( @Step , 0 )
-- Do random delay between 1 to 45 seconds
SELECT @Delay = CONVERT( varchar, DATEADD( ss, CEILING( RAND()* 44 ),
'1970-01-01') , 8 )
WAITFOR DELAY @Delay
UPDATE ##Progress
SET ItTookSoLong = datepart( ss, convert( datetime , @Delay ) ) ,
@Step = Step + 1
WHERE Step = @Step
END
GO
-- In your app, you can do:
-- Calculate percentage based on total number of steps
SELECT MAX( Step ) % 1000,0 AS StepPer
FROM ##ProgressHTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It's very frustrating that print messages don't appear when they are printed.
Frank's suggestion of insert rows into a temporary table is about the only practical solution I have come across. You could write the message out to a text file and read it a text viewer - I use notetab (www.notetab.com) as it doesn't lock the text file and prompts you when the file is changed.
Jeremy
-
I've started using RAISERROR a lot more than PRINT. If you use it with severity of 10 (rather than severity 16, which most people use to raise ad-hoc errors), it more or less functions like sprintf by allowing simple variable replacement:
RAISERROR('%d rows processed', 10, 1, @@ROWCOUNT)
Check out the NOWAIT option described by BOL as this might solve your WHILE problem.
Mark
Edited by - mdenner on 08/03/2003 03:59:12 AM
-
I would recommend using a user-defined trace, use Profiler Event "UserConfigurable:0" to watch it:
DECLARE @DebugStr NVARCHAR(100)
SET @DebugStr = @eventdate + ' ' + convert(varchar(6), @amount)
-- Log Custom message to Profiler User Defined Event 0)
EXEC sp_trace_generateevent 82, @DebugStr-Dan
-Dan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply