Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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
where....

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?

0
kennon2000
Asked:
kennon2000
  • 4
  • 2
  • 2
  • +2
1 Solution
 
SimonLarsenCommented:
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
0
 
LowfatspreadCommented:
is it always the same function?

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

 
0
 
billy21Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anthony PerkinsCommented:
Lowfatspread,

>>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:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21214230.html
0
 
LowfatspreadCommented:
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

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

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

etc..
0
 
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.
Thanks.

0
 
billy21Commented:
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'.
0
 
Anthony PerkinsCommented:
billy21,

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.
0
 
billy21Commented:
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.

0
 
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.
Thanks
0
 
billy21Commented:
Kennon,

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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now