how to avoid calling function twice? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

how to avoid calling function twice?

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-19 : 12:00:11

Greetings

How can I do this UPDATE better?

UPDATE VCapacities
SET Capacity = dbo.int_Capacity(BranchID)
WHERE CapacityDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND Capacity <> dbo.int_Capacity(BranchID)

should I do @Capacity = dbo.int_Capacity(BranchID)

then
UPDATE VCapacities
SET Capacity = @Capacity
WHERE CapacityDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND Capacity <> @Capacity

Thanks

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-19 : 12:15:51

hmm I guess I don't understand this syntax:

SET Capacity = dbo.int_Capacity(BranchID)

Are you trying to call a function? Because dbo.int_Capacity is being referenced as a table.

Are you trying to set capacity to a static value or is it being referenced from int_Capacity?

Anyway if you are trying to compare against another table I think this may work for you.

UPDATE Vcapacity
SET Capacity = ( SELECT SomeTable.BranchID
FROM SomeTable
WHERE CapacityDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND Capacity <> SomeTable.BranchID
WHERE EXISTS
(SELECT SomeTable.BranchID
FROM SomeTable
WHERE CapacityDate = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND Capacity <> SomeTable.BranchID)

The Where Exists clause will return a boolean (true or false). If it returns true the update will occur otherwise it will not.

Hope that helps !

r&r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 12:34:22

i think first is right. how will you store values of capacity for each row in a variable?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-19 : 12:47:24

oops that is a problem so I have to do it the first way..or create a table variable with BranchID I can later join to, I am just worried about performance as int_Capacity function will have some serious mathematical calculations hitting multiple tables in it...hence my question ..avoiding calling int_Capacity twice.
So if I can create a table variable as such
DECLARE @CapacityByBranch table(
BranchID int NOT NULL,
BranchCapacity int);
then INSERT INTO @CapacityByBranch
SELECT BranchID,
int_Capacity(BRanchID)
FROM syBranches
then how do I go using this temp table to do the original UPDATE without calling the function twice?

THANK U

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 12:51:22

quote:
Originally posted by yosiasz

oops that is a problem so I have to do it the first way..or create a table variable with BranchID I can later join to, I am just worried about performance as int_Capacity function will have some serious mathematical calculations hitting multiple tables in it...hence my question ..avoiding calling int_Capacity twice.
So if I can create a table variable as such
DECLARE @CapacityByBranch table(
BranchID int NOT NULL,
BranchCapacity int);
then INSERT INTO @CapacityByBranch
SELECT BranchID,
int_Capacity(BRanchID)
FROM syBranches
then how do I go using this temp table to do the original UPDATE without calling the function twice?

THANK U

just use


UPDATE t
SET t.Capacity=cb.BranchCapacity 
FROM syBranches t
INNER JOIN @CapacityByBranch cb
ON cb.BranchID=t.BranchID
WHERE t.Capacity<>cb.BranchCapacity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources