> Basically I want to display the time taken to run the SP.
> Therefore want to get the current_time before & after the while loop.
>
>
> Following is just the demo SP used to display the current time in a
> loop. But the problem is that each time, output prints the same time.

As designed...

If you want new times, use 'NOW' instead.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> SET TERM !! ;
>
> CREATE OR ALTER PROCEDURE LOOPTIME RETURNS (T VARCHAR(25))
> AS
> DECLARE VARIABLE I INTEGER;
> BEGIN
> I = 0;
> WHILE (I < 10000) DO
> BEGIN
> T = CAST(CURRENT_TIME AS VARCHAR(25));
> SUSPEND;
> I = I + 1;
> END
> END !!
>
> SET TERM ; !!
>
> SELECT T FROM LOOPTIME;