keeping math function - 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)

keeping math function

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-20 : 19:37:28

hello all i have this cursor within a trigger as well as other stuff

i want to try to remove the cursor but hold the math function and i just cant get my head around it

SET @TotalEntitySize = @TotalEntitySize + @typeSize * @TotalDimensions

that line is the problem

DECLARE entTypeDimCursor CURSOR LOCAL FAST_FORWARD
FOR SELECT EntityItemId, TypeId, TotalDimElements FROM ENTITY_ITEM
WHERE EntityId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0
OPEN entTypeDimCursor
FETCH NEXT FROM entTypeDimCursor INTO @EntityID, @TypeIdentifier, @TotalDimensions
WHILE @@fetch_status = 0
BEGIN

SELECT @autoSize = AutoSize FROM dbo.ENTITY
WHERE EntityId = @TypeIdentifier AND MajorBuildNo = 0 AND MinorBuildNo = 0

IF (@autoSize = 1)
BEGIN
SELECT @typeSize = NewSizeOfEntity FROM dbo.ENTITY
WHERE EntityId = @TypeIdentifier AND MajorBuildNo = 0 AND MinorBuildNo = 0
END
ELSE
BEGIN
SELECT @typeSize = NewSizeOfEntity + SparesInBytes FROM dbo.ENTITY
WHERE EntityId = @TypeIdentifier AND MajorBuildNo = 0 AND MinorBuildNo = 0
END

SET @TotalEntitySize = @TotalEntitySize + @typeSize * @TotalDimensions

FETCH NEXT FROM entTypeDimCursor INTO @EntityID, @TypeIdentifier, @TotalDimensions
END
CLOSE entTypeDimCursor
DEALLOCATE entTypeDimCursor

i started doing something like this but of cource the maths wont work out the same

SELECT @typeSize = @typeSize + SUM(NewSizeOfEntity FROM dbo.ENTITY
WHERE EntityId in (SELECT TypeId FROM ENTITY_ITEM
WHERE EntityId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0 )
AND MajorBuildNo = 0 AND MinorBuildNo = 0 AND AutoSize = 1)

SELECT @typeSize = @typeSize + SUM(NewSizeOfEntity + SparesInBytes FROM dbo.ENTITY
WHERE EntityId = (SELECT TypeId FROM ENTITY_ITEM
WHERE EntityId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0 )
AND MajorBuildNo = 0 AND MinorBuildNo = 0 AND AutoSize <> 1)

SET @TotalEntitySize = @TotalEntitySize + @typeSize * SUM(SELECT TotalDimElements FROM ENTITY_ITEM
WHERE EntityId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0)

any ideas?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-20 : 19:49:31

What is "The other stuff" ?
What are you doing with @TotalEntitySize after the cursor operation?

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-20 : 19:52:17

doing an update

UPDATE dbo.ENTITY SET NewSizeOfEntity = @TotalEntitySize, SizeUpdated = 1
WHERE EntityId = @EntityId AND MajorBuildNo = 0 AND MinorBuildNo = 0

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-20 : 22:39:24

show some sample data... and the expected result

It's much easier to work with visuals!

Corey

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-21 : 14:06:43

select
sum (
(e.NewSizeOfEntity +
case when e.AutoSize = 1 then 0 else e.SparesInBytes end)
* ei.TotalDimElements
) TotalEntitySize
from dbo.Entity e
join dbo.Entity_Item ei
on ei.TypeID = e.EntityID
where ei.EntityID = @EntityID
AND MajorBuildNo = 0
AND MinorBuildNo = 0

HTH

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

The best things in life are nearest: Breath in your nostrils, light in your eyes, flowers at your feet, duties at your hand, the path of right just before you. -Robert Louis Stevenson, novelist, essayist, and poet
(1850-1894)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources