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.
| Author |
Topic |
|
Jawad Khan
Starting Member
21 Posts |
 Posted - 2008-10-21 : 02:43:15
|
| HiCan I store a formula in a field and later use it in query?For example I want the following query;Select RIGHT(FieldName, 3) as DisplayName.....Can I store RIGHT(FieldName, 3) in a column so that it can be changed later to another format (Say RIGHT(FieldName, 5) and execute query in such a waySelect myformula as DisplayName....Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-21 : 02:50:29
|
| nope. you cant just execute formula by storing it in a column and selecting it. you need to use dynamic sql for it.and its also not a good approach to do it that way. You could directly use formula in select query rather than storing it somewhere and then executing as dynamic sql. could you explain why you want to do it this way? |
 |
|
|
Jawad Khan
Starting Member
21 Posts |
Posted - 2008-10-21 : 02:56:20
|
| Well I just wanted to be more flexible about DisplayName format. The management may decide to change the display name format. so I thought it would be good to store formula so that later I can change it rather than changing the queries. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-21 : 03:00:34
|
quote:
Originally posted by Jawad Khan
Well I just wanted to be more flexible about DisplayName format. The management may decide to change the display name format. so I thought it would be good to store formula so that later I can change it rather than changing the queries.
if its only number of characters displayed that varies, just declare an integer parameter (say @n) and use it like belowRIGHT(FieldName, @n) |
|
|
|
Jawad Khan
Starting Member
21 Posts |
Posted - 2008-10-21 : 03:04:29
|
| Nope, The whole formula might change. e.g. RIGHT(DeptName,3) + RIGHT(FieldName, 3)I think it is better to store them in Visual Basic variables and send the query programatically. Thanks by the way. I just needed to confirm this from gurus :-) |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-21 : 08:22:10
|
| you can create a view or computed column. it would need some minor change but might help localise the change. be careful if you join to it though because you may not be able to index it.generally though you want to do your display stuff away from the db |
|
|
|
|
|
|
Store formula in column as string