SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Progress Bar Simulation on SQL Server


Progress Bar Simulation on SQL Server

Author
Message
rcherod
rcherod
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 222
Comments posted to this topic are about the item Progress Bar Simulation on SQL Server
hannes.malan
hannes.malan
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 249
Unfortunately I do not understand the language of the article but surely you can use the REPLICATE function to populate your pipe symbols...
e.g.
SET @exec1 = 'update a set progreso=replicate(''|'','+@c+'),descrip = (select ''Progreso ... ''+convert(varchar(10),'+ @c +')+'' %      Creditos ™ : Richard J.Chero Díaz ''   ) from progress a   where  descrip like ''Progreso%''
'


rcherod
rcherod
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 222
Thank you very much for reply

I try Replicate commnad and that´s better to optimize the script. ;-)
rcherod
rcherod
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 222
modified code :-D

-- ======================================================
-- Author: [Chero Díaz,Richard Johnnatan]
-- DBA.
-- Create date: [2013,11,28]
-- Description: [Simulación de Barra de Progreso]
-- © Copyright - Todos los Derechos reservados ™
-- ======================================================
---__________________________________________________________---
--- Creando e insertando registros a mi tabla Conteo ---
---__________________________________________________________---

--- Eliminamos la tabla si existe
if exists (select * from sysobjects
where id = object_id(N'[dbo].[conteo]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[conteo]
--- Creamos tabla de conteo es decir los rangos de progreso
CREATE TABLE conteo (progreso int)
--- Inserto el primer valor de progreso en mi tabla conteo
insert into conteo values ('1')
--- Inserto los 100 valores en mi tabla conteo dinamicamente
WHILE (select max(progreso)+1 from conteo where progreso < 101) < 101
BEGIN
insert into conteo select max(progreso)+1 from conteo where progreso < 101
END
--- select * from conteo
/*_______________________________________________________________________________________*/


---__________________________________________________________---
--- Creando e insertando registros a mi tabla progress ---
---__________________________________________________________---

--- Eliminamos la tabla si existe
if exists (select * from sysobjects
where id = object_id(N'[dbo].[progress]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[progress]
--- Creamos tabla de fechas es decir los dias del mes
CREATE TABLE progress (progreso varchar(255),descrip varchar(255))
--- Insertamos registro 0
insert into progress values(' ','Progreso ... 0 % Creditos ™ : Richard J.Chero Díaz ')


---select * from progress
/*_______________________________________________________________________________________*/

---Cursor Progreso
-- variable ----------------------------------------------
DECLARE @PROGRESO nvarchar(255)
-- variables para definir las consultas en tiempo de ejecución ------------------
DECLARE @exec1 nvarchar(4000)
DECLARE @exec2 nvarchar(4000)
DECLARE @exec3 nvarchar(4000)
-- declarar el cursor -----------------------------------------------------------
DECLARE RECORRE CURSOR FOR
SELECT * from conteo
-- abrir cursor -----------------------------------------------------------------
OPEN RECORRE
FETCH NEXT FROM RECORRE INTO @PROGRESO
-- mientras haya datos... -------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
-- por cada fila ----------------------------------------

declare @b as varchar(100)
declare @c as varchar(100)
set @b = @PROGRESO
set @c = @b

SET @exec1 = 'update a set progreso=replicate(''|'','+@c+'),descrip = (select ''Progreso ... ''+convert(varchar(10),'+ @c +')+'' % Creditos ™ : Richard J.Chero Díaz '' ) from progress a where descrip like ''Progreso%''
'
IF (select @c)<101
BEGIN
print @exec1
EXECUTE (@exec1)
END


-- ======================================================
-- Author: [Chero Díaz,Richard Johnnatan]
-- DBA.
-- Create date: [2013,11,28]
-- Description: [Simulación de Barra de Progreso]
-- © Copyright - Todos los Derechos reservados ™
-- ======================================================
select * from [progress]
-----------------------------------------------------------------------------------
------ siguiente registro ---------------------------------------------------------
FETCH NEXT FROM RECORRE INTO @PROGRESO
END
-- cerrar y liberar la memoria del cursor -----------------------------------------
CLOSE RECORRE
DEALLOCATE RECORRE
Sean Smith (SSC)
Sean Smith (SSC)
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 1002
Interesting.

You could also do something like this:


WITH CTE AS

(
SELECT
1 AS number

UNION ALL

SELECT
X.number + 1 AS number
FROM
CTE X
WHERE
X.number + 1 <= 100
)

SELECT
REPLICATE ('|', X.number) + REPLICATE ('.', 100 - X.number) AS progress_bar
,CONVERT (VARCHAR (11), X.number) + '%' AS progress_pct
FROM
CTE X


rcherod
rcherod
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 222
Excellent,:-)

thank you very much ..!!!
Sean Smith (SSC)
Sean Smith (SSC)
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 1002
Neat concept. Smile
SQLQuest29
SQLQuest29
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1320 Visits: 4325
Sean Smith-776614 (12/11/2013)
Interesting.

You could also do something like this:


WITH CTE AS

(
SELECT
1 AS number

UNION ALL

SELECT
X.number + 1 AS number
FROM
CTE X
WHERE
X.number + 1 <= 100
)

SELECT
REPLICATE ('|', X.number) + REPLICATE ('.', 100 - X.number) AS progress_bar
,CONVERT (VARCHAR (11), X.number) + '%' AS progress_pct
FROM
CTE X



Excellent and much neater code ..... w00t

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor :-)
gingo
gingo
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 162
other way to make progress bar for each 10% only



declare
@MAX decimal(10,3)
, @CNT decimal(10,3)
, @ProgressBar varchar(250)
, @Percentage int
, @PercentageStep int

set @MAX = 8745.458
set @CNT = 1.0
set @PercentageStep = 10

WHILE @CNT <= @MAX
BEGIN
Set @CNT = @CNT + 1
IF ((@CNT*100/(ceiling(@MAX/@PercentageStep)*@PercentageStep)) % @PercentageStep) in ( 0.00)
BEGIN
set @Percentage = @CNT*100/(ceiling(@MAX/@PercentageStep)*@PercentageStep)
set @ProgressBar = replicate('X',@Percentage/2)+' '+ltrim(rtrim(str(@Percentage)))+' '+replicate('_',(100-@Percentage)/2)
raiserror(@ProgressBar, 0, 1) with nowait
END
END
dsorensen 97289
dsorensen 97289
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 63
This is a neat idea, but am I missing something? How would one put this into use as an actual progress bar? For example, to estimate how far along you are on a long running update query?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search