MS SQL SERVER 2005 - UPDATE STATEMENT WITH TWO FUNCTIONS INSIDE...

GlobaLevel
GlobaLevel used Ask the Experts™
on
I need to run an update statement with the data being revisd thru two functions:
ts.home_page has to go thru dbo.propercase..before it run thru dbo.final_edit...

Would it look like this??




Update tc

Set HOME_PAGE  = dbo.final_edit(dbo.ProperCase(ts.HOME_PAGE ))

FROM product tc

 Join @temp_prod_table ts

 ON tc.wit_id = ts.wit_id
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
That's right. Are you getting an error?
GlobaLevelProgrammer

Author

Commented:
no just wanted to be sure....
GlobaLevelProgrammer

Author

Commented:
and if I also need to pass a parameter '25' into the final_edit function then this?


Update tc

Set HOME_PAGE  = dbo.final_edit((dbo.ProperCase(ts.HOME_PAGE )),25)

FROM product tc

 Join @temp_prod_table ts

 ON tc.wit_id = ts.wit_id
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Most Valuable Expert 2015
Commented:
You could omit the extra parenthesis around the inner function call

ie  ... Set HOME_PAGE  = dbo.final_edit( dbo.ProperCase(ts.HOME_PAGE), 25)

... But either way should work fine.
GlobaLevelProgrammer

Author

Commented:
is there a difference in performance?
Most Valuable Expert 2015

Commented:
From using 2 functions versus 1 function? Most likely yes.  But unless the query has a lot of records OR the 2 functions are doing something really complicated it's usually not a big concern.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
> is there a difference in performance?

Between whether or not you add an unnecessary bracket? - no
Select 1 is the same as select (1) and select ((((1)))).  By the time the parser (that which takes a statement and breaks into logical bits) is done with the statement, they are the same thing.

If you are asking about calling the function twice vs creating some function that does both things at once, then the answer could vary.  But given your function names, I am pretty sure the same things will be done resulting in same CPU usage, so it wouldn't matter.
Most Valuable Expert 2015

Commented:
BTW: What are your functions doing? Simple conditional logic OR do they contain subqueries  .. ?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
So long as your functions are returning a single value you should be fine.

Functions are not the quickest of beasts so cascading them is not the most efficient way to run a big update, but, if they do the job then that is more important than speed in some regards - so long as performance is acceptable...

Could also consider running the home_page transformation when building the @temp_prod_table, or, possibly resolving it at a subquery level (ie " join (select ... from @temp_prod_table) tc on..." ) and joining to that.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial