Any best practices to get complex calculated field from table?

I always encounters a kind of problem in querying SQL which I am looking for a best practiced to solve it.  In SELECT query like

SELECT field1, field2, ComplexFieldToBeCalculate, ....
from Table1

there are fields that I want to get from complex calculation.  I currently use two methods:
1. Get recordset with the ComplexFieldToBeCalculate not calculated, then recalculate the field by looping through all rows of the recordset.  This method works all the time, but obviously not efficient.
2. Write function and use it like SELECT field1, field2, GetComplexField(field1, field2..), ....this method is good but function have some limitations like cannot execute dynamic SQL.

What is the best practice to get that kind of fields?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you looked at writing a UDF (user defined fuinction) for the complex expression then calling it?

Select field1, field2, dbo.fn_YourFunction (param1, param2)
from Table1
is it always the same function?

can you explain whats complex about the function and why you feel you need to use dynamic sql?

Best practices would be to use a UDF.  However, if the process of calculating this field is so intense that it is slowing things down too much, best practices says store the result and update it with triggers.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:

>>can you explain whats complex about the function and why you feel you need to use dynamic sql?<<
I believe this is what the questioner is referering to:
not sure without a better description of the context...

but another approach would be to utilise a temporary table..

and basically create the temp table with the basic  calculation performed in that

select a,b,dbo.myfnc(a,b,c,d,e,f) as r
into #temp
from  ..

select a,b,r,d,f,e
from #temp
inner join ....

kennon2000Author Commented:
Yes, I have ready used a UDF (user defined fuinction) as shown in my question.

I cannot show you all the case that I describe as 'Complex' calculation.  But you can imagine how complicated a dynamic sql statement can perform.  Dynamic SQL is the most flexible method.  Of cause, for less complex cases, I can use if...else logic in SQL query instead of forming the query dynamically, but it is not an answer to complicated logic as it is difficult to use if...else to implement all possible combination.

Actually, I just wander why EXEC is limited in UDF.
Also, temporary object is not allowed in UDF too.

I think billy21 is giving me the closest answer.  But I want to see other possible practices that you experts use as this kind of problem trouble me a lot when I write T-SQL.

I can't see how any complex calculation would require dynamic SQL.  Dynamic SQL should be avoided at all costs.  The only legitimate use for DSQL I can think of is throughmaintenance.  Ie. writing a proc to delete every stored procedure/index/udf.

Dynamic SQL has to be compiled on the fly making it less efficient and it's less readable and much much messier!  I've never come accross a situation where i've wanted to use 'exec' in a UDF and haven't been able to find an alternative.  If you can help us to know what it is you're trying to do we can probably find a solution for you that doesn't require DSQL.  I'd say that every experienced developer offering assistance on this web site would instinctively look for alternative methods as soon as their hear the term 'Dynamic SQL'.
Anthony PerkinsCommented:

I could not agree more and I would add that Dynamic SQL's biggest disadvantage is security (or the lack of).  To the point that many shops don't even allow it in applications.
Yes.  Kennon 2000, perhaps you should do a quick search for 'SQL Insertion Attacks'.  This is where the user enters something like '';Drop Table Customers; into a field such as a username.

';' tells sql server that one statement has finished and the next one will begin.  It then executes the drop table statement.

kennon2000Author Commented:
But even you use variables say @username, 'SQL Insertion Attacks' can still work...Anyway, I think I get a lot to learn from you experts.

But even you use variables say @username, 'SQL Insertion Attacks' can still work...Anyway, I think I get a lot to learn from you experts.

When you parse in username and embed it into an sql statement and you're attacked the resulting SQL is something like this...

Assuming the user enters their username as ';Drop Table Customers;'

Select *
From Users
Where Username = '';Drop Table Customers;''

It then executes the first query returning no rows and executes the second statement dropping the table.

When I receive a parameter the resulting query looks like this...
Select *
From Users
Where Username = @Username

It doesn't matter what's in the variable username.  The entire thing is treated as 1 string.

SO NO you are wrong in your statement.

The above assumes the stored procedure is declared with brackets as so...
Create Procedure MyProc (@Username)

AS Opposed to
Create Procedure MyProc @Username

The latter would leave me vulnerable to sql insertion attack.  The former would not.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.