kennon2000
asked on
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?
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?
is it always the same function?
can you explain whats complex about the function and why you feel you need to use dynamic sql?
can you explain whats complex about the function and why you feel you need to use dynamic sql?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
https://www.experts-exchange.com/questions/21214230/Dynamic-SQL-in-User-defined-function.html
>>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:
https://www.experts-exchange.com/questions/21214230/Dynamic-SQL-in-User-defined-function.html
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)
into #temp
from ..
then
select a,b,r,d,f,e
from #temp
inner join ....
etc..
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)
into #temp
from ..
then
select a,b,r,d,f,e
from #temp
inner join ....
etc..
ASKER
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.
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.
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'.
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'.
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.
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.
';' tells sql server that one statement has finished and the next one will begin. It then executes the drop table statement.
ASKER
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
Thanks
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.
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.
Select field1, field2, dbo.fn_YourFunction (param1, param2)
from Table1