setting a COMPUTE nubmer to a local variable

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

Transact-SQL (2000)

setting a COMPUTE nubmer to a local variable

Author

Topic

Zathras
Starting Member

28 Posts

Posted - 2004-03-19 : 12:51:03

This is part of a stored proceedure. I want to assign the computed value at the end to the local variable @LastTime so I can use it elsewhere in my stored proceedure. I tried COMPUTE @LastTime=MAX(TimeStamp), but that gave an error. Anyone know how to do this? Or you know another way to get the latest timestamp of all these tables?

DECLARE @LastTime as smalldatetime
DECLARE @ThisProject as varchar(10)
SET @ThisProject=1410

SELECT TimeStamp FROM PDMSExport.dbo.PipingTube WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.PipingFitting WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.PipingExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.EquiEquipment WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.EquiExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecStraightTray WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecTrayFitting WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecUDA WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstInstrument WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstStraightTray WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstTrayFitting WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstUDA WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.StruSctnAndGensec WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.StruPane WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.StruExportError WHERE ProjNo = @ThisProject
COMPUTE MAX(TimeStamp)

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 13:01:26

[code]

SELECT @LastTime=MAX([Timestamp]) FROM(
SELECT TimeStamp FROM PDMSExport.dbo.PipingTube WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.PipingFitting WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.PipingExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.EquiEquipment WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.EquiExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecStraightTray WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecTrayFitting WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.ElecUDA WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstInstrument WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstStraightTray WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstTrayFitting WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstExportError WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.InstUDA WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.StruSctnAndGensec WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.StruPane WHERE ProjNo = @ThisProject
UNION SELECT TimeStamp FROM PDMSExport.dbo.StruExportError WHERE ProjNo = @ThisProject
) AS XXX
[/code]

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-19 : 13:07:34

I think it would be more processor and memory friendly to use:

SELECT @LastTime=MAX([Timestamp]) FROM(
SELECT MAX(TimeStamp) FROM PDMSExport.dbo.PipingTube WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.PipingFitting WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.PipingExportError WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.EquiEquipment WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.EquiExportError WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.ElecStraightTray WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.ElecTrayFitting WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.ElecExportError WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.ElecUDA WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.InstInstrument WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.InstStraightTray WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.InstTrayFitting WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.InstExportError WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.InstUDA WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.StruSctnAndGensec WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.StruPane WHERE ProjNo = @ThisProject
UNION ALL SELECT MAX(TimeStamp) FROM PDMSExport.dbo.StruExportError WHERE ProjNo = @ThisProject
) AS XXX

This would be especially true if there is an index on TimeStamp on these tables.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 13:09:01

I bet ProjId is the key though....

Brett

8-)

Zathras
Starting Member

28 Posts

Posted - 2004-03-19 : 13:36:10

Your TSQL does work; thanks so much! Yes, ProjNo along with an ID field (1,2,3,4,5,...) are the primary keys. Since the TimeStamp field is not a key, I assume that mean the first posted code would then run faster?

I did try what Brett posted originaly, but it didn't have the AS XXX statement and so it gave an error saying "Incorrect syntax near ')'", I didn't realise the AS statement was required in this situation. Thanks a lot guys!

Zathras
Starting Member

28 Posts

Posted - 2004-03-19 : 13:47:50

Derrick's modification definately runs faster.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 13:57:23

Really? It has to do more work....

Can you tell us what the plan says for both?

Do you have a clustered index on projno?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources