Link to home
Start Free TrialLog in
Avatar of LennyGray
LennyGrayFlag for United States of America

asked on

Executing SQL code within a table field

Hi All -

I have a SqlServer table that has this structure:
                                AccountNumber VARCHAR(50) NULL,
                                QRMYieldCIT DECIMAL(10,5) NULL,
                                TestResults DECIMAL(10,5) NULL,
                                Floor_Pricing_Txt_Formula VARCHAR(250) NULL,
                                TheFinalFormula VARCHAR(255) NULL,
                                CalculationResults DECIMAL(10,5) NULL  

In the table field, TheFinalFormula, there contains a statement that I would like to execute within a query/stored procedure that will fill the result of the formula into the table field, CalculationResults.

Each data record has its own formula in TheFinalFormula and the formula is not generally duplicated.

Here is a sample statement that would appear in the table field, TheFinalFormula:
SELECT(CommonPrograms.dbo.cp_GreaterUDF(125*.0001,.0625) + 5.5*.01)

Note: CommonPrograms.dbo.cp_GreaterUDF is a user-defined scalar function that selects the greater of two values.

Thanks for the help!

Lenny

How can I do this?
Avatar of Sharath S
Sharath S
Flag of United States of America image

Create your table with DEFAULT clause on TheFinalForumal column if possible.
CREATE TABLE your_table ( 
              AccountNumber VARCHAR(50)    NULL, 
                QRMYieldCIT DECIMAL(10,5)    NULL, 
                TestResults DECIMAL(10,5)    NULL, 
  Floor_Pricing_Txt_Formula VARCHAR(250)    NULL, 
            TheFinalFormula VARCHAR(255)    NULL    DEFAULT (dbo.CP_GREATERUDF(125 * .0001,.0625) + 5.5 * .01), 
         CalculationResults DECIMAL(10,5)    NULL)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LennyGray

ASKER

Thanks for the quick response.

I am looking for the table field, CalculationResults, to be filled with the result of the formula that is in the table field, TheFinalFormula. Each data record (row) would have a formula and then a calculted result of the formula.

For example, the formula, SELECT(CommonPrograms.dbo.cp_GreaterUDF(125*.0001,.0625) + 5.5*.01), that is in the table field, TheFinalFormula, would yield a result in CalculationResults that would be equal to 2.0525000000.

I hope that this explains what I am attempting.

Lenny

Thanks, tigin44.

I am getting an error

"Msg 207, Level 16, State 1, Line 1
Invalid column name 'USD_BC1C7_00003536_1CSLIBOR_O'  "

What does this mean?

Lenny
Account number is not unique, by the way.
its probably that some of the formulas referencing a column named 'USD_BC1C7_00003536_1CSLIBOR_O' which is not in your table... check the formula column...
if account number is not uniquw than this will not work... you may change AccountNumber with a unique column name or add a new column with a unique value to get this code to work ..
What is the SQL equivalent to the MsAccess "autonumber"?

I could add a field to the table named TheIndex before I use your process, I tried uniqueidentifier and it did not automatically insert a unique number.

I am creating the MajorGroups.dbo.FortranTable
table that has the formula with the following code:

INSERT INTO   MajorGroups.dbo.FortranTable
SELECT   AccountNumber,
              QRMYieldCIT,
              @TheTestResults,
              Floor_Pricing_Txt_Formula,
              'SELECT(' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Floor_Pricing_Txt_Formula, 'PRIME', @ThePrime), 'HigherOf', 'CommonPrograms.dbo.cp_GreaterUDF'), 'BP', '*.0001'), 'F3LIB', @TheF3LIB), 'LIBO', @TheLIBO), 'Spread(', ''), '%)', '*.01') + ')',
              NULL
FROM          InputHistory

I appreciate your help since I am just learning Sql coding after using Vb and Access for the past 15 years.

Thanks again.

ALTER TABLE dbo.AAA ADD
      IDcolumn int NOT NULL IDENTITY (1, 1)
Brilliant work!!!

Thanks!