Worst use of a cursor

-- Create the table to accept the results

create table #output (dbname char(30),log_size real, usage real, status int)

-- execute the command, putting the results in the table

insert into #output

exec ('dbcc sqlperf(logspace)')

-- display the results

select *

from #output

go

--open cursor

declare output_cur cursor read_only for

select log_size

from #output

--make space computations

declare @v1 as real

declare @base_val as real

set @base_val = 0

open output_cur

FETCH NEXT FROM output_cur

INTO @v1

WHILE @@FETCH_STATUS = 0

BEGIN

set @base_val = @base_val + @v1

FETCH NEXT FROM output_cur

INTO @v1

END

set @base_val = @base_val + 15

PRINT 'BASE_VAL = ' + cast(@base_val as nvarchar) + ' MB'

--clean-up

close output_cur

drop table #output

This has to be the worst use of a cursor I’ve seen for a long time. Has this company not know about SUM().

Have you seen anything worse?

Published 08 September 2010 15:20 by simonsabin
Filed under: , ,

Comments

08 September 2010 16:05 by jamiet

# re: Worst use of a cursor

name and shame Si, name and shame!!!

08 September 2010 16:39 by SimonS Blog on SQL Server Stuff

# Worst use of a cursor

-- Create the table to accept the results create table #output ( dbname char ( 30 ), log_size real ,

08 September 2010 22:32 by DavePoole

# re: Worst use of a cursor

Not even close to the wort I've seen.

Just to pseudo code the worst one

CREATE PROC dbo.WTF

 @PK INT,

 @CustomerType DECIMAL(18,0)

AS

DECLARE @NewCustomerType DECIMAL(18,0)

DECLARE PointlessCursor CURSOR FOR

SELECT CustomerType FROM dbo.Table WHERE PK = @PK

open PointlessCursor

FETCH NEXT FROM PointlessCursor

INTO @NewcustomerType

WHILE @@FETCH_STATUS = 0

BEGIN

IF @NewCustomerType>1

  EXEC dbo.WTF @PK,@CustomerType

FETCH NEXT FROM PointlessCursor

INTO @NewCustomerType

END

CLOSE PointlessCursor

DEALLOCATE PointlessCursor

To cap it all the valid range of values for which the author had specified DECIMAL(18,0) were 1 to 3.

I could fill a book with examples from the guy who wrote the original code.  His code is so bizarre I can't make up my mind whether he is a surviving brain donor or an out-and-out genius.

09 September 2010 08:06 by simonsabin

# re: Worst use of a cursor

Jamiet, just look at the tags

Dave, nice example. Some people do have an inate ability to make things more complex than they need to be