Using Dynamic Query in a Transact SQL Select

• salliven

  SSCrazy

  Points: 2644

  More actions

  August 24, 2017 at 4:30 am

  #1956561

  imanalimi - Tuesday, August 22, 2017 8:49 PM

  Hi Guys,
  I am trying to calculate formula  using SQL Server 2016.
  How can I get the result of a Formula within a Select Statement like this:
  
  CREATE TABLE #tblFormula (C1 INT, C2 INT,Formula NVARCHAR(50))
  INSERT INTO #tblFormula (C1,C2,Formula)
  VALUES
  (5,1,'C1+C2')
  ,(7,2.5,'C1*C2')
  ,(4.5,8,'C1/C2');

  SELECT C1,C2,Formula,'Result' AS [Result] FROM #tblFormula

  DROP TABLE #tblFormula;

  The Result would be similar to:
  C1....C2....Formula....Result
  5........1.....C1+C2........6
  7........2.....C1*C2........14
  4........8.....C1/C2........0.5

  Many Thanks

  What do you think about the Eval CLR?
  http://eval-sql.net/
  https://github.com/zzzprojects/Eval-SQL.NET/wiki/SQL-Server-Eval-%7C-Dynamically-evaluate-arithmetic-operation-and-expression-in-SQL

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  August 24, 2017 at 11:13 am

  #1956675

  I'm thinking that you really only need one table here, and it would be the solution table.   Then the cursor could do an UPDATE to that table and the results could be later selected from that table.   Not sure if that will perform better, but for any seriously large volume, it might be a better way to operate.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)