if elseif - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

if elseif

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-23 : 19:53:37

Hey guys i got a problem converting this cursor :) into set based. And i was doing so well with all the others, just got to get my head around this one
Its the if else statment that is buggaring me up
DECLARE iterDeleted CURSOR FAST_FORWARD
FOR
SELECT DIMENSIONNO, DEFINEID FROM deleted where MAJORBUILDNO = 0 and MINORBUILDNO = 0
OPEN iterDeleted
DECLARE
@DIMENSIONNO int , @DEFINEID int, @ENTITYITEMID int

FETCH NEXT FROM iterDeleted INTO @DIMENSIONNO, @DEFINEID, @ENTITYITEMID
WHILE @@fetch_status = 0
BEGIN

DECLARE @Evaluated int
SELECT @Evaluated = Evaluated FROM dbo.DEFINE
WHERE defineid = @DEFINEID AND MajorBuildNo = 0 AND MinorBuildNo = 0

IF (@DIMENSIONNO = 0)
BEGIN
UPDATE ENTITY_ITEM_VIEW
SET TotalDimElements = 1
WHERE EntityItemID = @ENTITYITEMID AND MajorBuildNo = 0 AND MinorBuildNo = 0
END
ELSE IF (@Evaluated > 0)
BEGIN
UPDATE ENTITY_ITEM_VIEW
SET TotalDimElements = TotalDimElements / @Evaluated
WHERE EntityItemID = @ENTITYITEMID AND MajorBuildNo = 0 AND MinorBuildNo = 0
END

FETCH NEXT FROM iterDeleted INTO @DIMENSIONNO, @DEFINEID, @ENTITYITEMID
END
CLOSE iterDeleted
DEALLOCATE iterDeleted

any help would be appricated

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-23 : 20:29:17

Its possible your Select assignment for @Evaluated returned Null

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-12-23 : 20:30:53

See if you can convert these to using #Temp tables with While loops and get away from cursors. It looks like you could use Insert into #Temp and loop through the recordsets with While.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-24 : 02:06:53

Is this within a trigger? If so, what is the base table?

HTH

=================================================================

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-24 : 02:31:57

You are missing ENTITYITEMID in your cursor definition ???????

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-24 : 02:37:59

replace all the code with these two update statement.. Does this work...?


UPDATE A
   SET TotalDimElements = 1
  FROM ENTITY_ITEM_VIEW A, 
       deleted          B
 WHERE A.EntityItemID = B.ENTITYITEMID AND A.MajorBuildNo = 0 AND A.MinorBuildNo = 0
   AND B.DIMENSIONNO = 0

UPDATE A
   SET TotalDimElements =  TotalDimElements / C.Evaluated
  FROM ENTITY_ITEM_VIEW A, 
       deleted          B, 
       dbo.DEFINE       C
 WHERE A.EntityItemID = B.ENTITYITEMID AND A.MajorBuildNo = 0 AND A.MinorBuildNo = 0
   AND B.DIMENSIONNO <> 0
   AND C.defineid = B.DEFINEID AND C.MajorBuildNo = 0 AND C.MinorBuildNo = 0

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-27 : 14:33:31

quote:
Originally posted by clarkbaker1964

See if you can convert these to using #Temp tables with While loops and get away from cursors. It looks like you could use Insert into #Temp and loop through the recordsets with While.

Using a WHILE loop is only slightly better than a cursor. Both require looping. Neither is set-based.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-27 : 15:15:43

I can name that update in 1 statement :)


UPDATE 
	e
SET 
	TotalDimElements = 

	CASE 
		WHEN d.DIMENSIONNO = 0 THEN 1 
		ELSE d.TotalDimElements / d.Evaluated
	END
FROM 
	Deleted d, ENTITY_ITEM_VIEW e
WHERE 
	e.ENTITYITEMID = d.ENTITYITEMID AND e.MajorBuildNo = 0 AND e.MinorBuildNo = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources