Solved

Executing SQL code within a table field

Posted on 2011-02-23
10
411 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:LennyGray
  • 5
  • 4
10 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34964162
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

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 34964261
try this where AAA is the tablename of your table





declare @sql   varchar(255);
DECLARE @AccountNumber varchar(50);

DECLARE cOrder CURSOR FOR
SELECT AccountNumber 
FROM AAA;
OPEN cOrder;
FETCH NEXT FROM cOrder INTO @AccountNumber
WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @sql =  'UPDATE T ' +
					'SET T.CalculationResults = (' + TheFinalFormula + ') ' +
					'FROM AAA T ' +
					'WHERE AccountNumber = ' + @AccountNumber
	FROM AAA
	WHERE AccountNumber = @AccountNumber

	EXEC (@sql)				
	FETCH NEXT FROM cOrder INTO @AccountNumber
END

Open in new window

0
 
LVL 10

Author Comment

by:LennyGray
ID: 34964282
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

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 10

Author Comment

by:LennyGray
ID: 34964338
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
0
 
LVL 10

Author Comment

by:LennyGray
ID: 34964351
Account number is not unique, by the way.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964367
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...
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964398
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 ..
0
 
LVL 10

Author Comment

by:LennyGray
ID: 34964601
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.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34964708

ALTER TABLE dbo.AAA ADD
      IDcolumn int NOT NULL IDENTITY (1, 1)
0
 
LVL 10

Author Closing Comment

by:LennyGray
ID: 34964898
Brilliant work!!!

Thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query 12 72
How to extract a "coded date" from a string field? 4 59
Order by but want it in specific order 2 33
Strange msg in the SSMS pane 13 55
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question