How to get math result from varchar formula?

• DavidCl

  Mr or Mrs. 500

  Points: 528

  More actions

  July 15, 2004 at 9:23 pm

  #108565

  I have a table with a varchar field that stores a formula used to calculate the list price of of products based on vendor etc.  The cost of the product is to be run against the formula to arrive at the list price.  The problem is how to run the math and get the result with out creating a small calculator program to do it.  The code below demonstrates the problem.

  declare @Cost Money

  declare @Formula varchar(100)

  select @Cost = 12.50

  select @Formula = '$ + 12 * 3'

  select @Formula = replace(@Formula, '$', convert(varchar(12), @Cost))

  Is there a way that I can execute @Formula to get the result?  I have tried execute and sqlexecute.  But I have not found any syntax that works.

  Thank you for your time.  I will appreciate any suggestions.

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  July 15, 2004 at 9:45 pm

  #514788

  Add after last of your statements:

  select @Formula = 'select ' + @Formula -- or 'print '

  execute (@Formula)

  and run.

  That's all.

  _____________
  Code for TallyGenerator

• DavidCl

  Mr or Mrs. 500

  Points: 528

  More actions

  July 17, 2004 at 6:47 pm

  #514993

  Thank You!  I thought it was something faily simple.  I just could not seem to get it.

  I have another question if you don't mind.  Now, I need to get the result into a variable.  I have gotten close but the exact syntax I have not figured out yet.  Here is what I have been trying:

  execute @Price = (@Formula)

  But this returns an error.  What am I missing?

  Thanks again.

• Derrick Leggett

  SSCrazy

  Points: 2778

  More actions

  July 17, 2004 at 8:05 pm

  #514998

  declare @Cost Money

  declare @Formula varchar(100)

  select @Cost = 12.50

  select @Formula = '$ + 12 * 3'

  select @Formula = replace(@Formula, '$', 'cast(' + convert(varchar(12), @Cost) + ' as int)')

  select @Formula = 'select ' + @Formula

  --print @Formula

  EXEC(@Formula)

  Derrick Leggett
  Mean Old DBA
  When life gives you a lemon, fire the DBA.

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  July 18, 2004 at 5:07 pm

  #515025

  You cannot use that syntax because @Formula is not SP.

  Actually options for EXECUTE ('String') are quite limited. It exists in own environment, no variables to be transferred.

  The only way I know is to use temp table. Something like this:

  declare @Cost Money, @Price money

  declare @Formula varchar(100)

  Create table tmp_R  (Result money)

  select @Cost = 12.50

  select @Formula = '$ + 12 * 3'

  select @Formula = replace(@Formula, '$', convert(varchar(12), @Cost))

  select @Formula = 'select ' + @Formula -- or 'print '

  insert into tmp_R

  execute  (@Formula)

  select @Price = result from tmp_R

  Drop table tmp_R

  But the better way - NOT TO USE DYNAMIC SQL! There are a dozen of ways to implement your sample task without executing string. I believe at least one of them is suitable for your real task. SPs, tables and views with calculated columns - all are ready for service.

  Good luck,

  Sergiy.

  _____________
  Code for TallyGenerator

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  July 18, 2004 at 5:07 pm

  #515024

  You cannot use that syntax because @Formula is not SP.

  Actually options for EXECUTE ('String') are quite limited. It exists in own environment, no variables to be transferred.

  The only way I know is to use temp table. Something like this:

  declare @Cost Money, @Price money

  declare @Formula varchar(100)

  Create table tmp_R  (Result money)

  select @Cost = 12.50

  select @Formula = '$ + 12 * 3'

  select @Formula = replace(@Formula, '$', convert(varchar(12), @Cost))

  select @Formula = 'select ' + @Formula

  insert into tmp_R

  execute  (@Formula)

  select @Price = result from tmp_R

  Drop table tmp_R

  But the better way - NOT TO USE DYNAMIC SQL! There are a dozen of ways to implement your sample task without executing string. I believe at least one of them is suitable for your real task. SPs, tables and views with calculated columns - all are ready for service.

  Good luck,

  Sergiy.

  _____________
  Code for TallyGenerator

• Derrick Leggett

  SSCrazy

  Points: 2778

  More actions

  July 18, 2004 at 7:29 pm

  #515031

  ?????  The example I gave does work.  Just run it.  I agree that dynamic SQL isn't the best way to do this.  It would be bettre to just create a fucntion that accepts an input parameter and returns the formula result.  The example I showed definitely works though.

  Derrick Leggett
  Mean Old DBA
  When life gives you a lemon, fire the DBA.

• DavidCl

  Mr or Mrs. 500

  Points: 528

  More actions

  July 19, 2004 at 8:54 am

  #515082

  That works Sergiy.  I do appreciate everyone’s help!