?
Solved

Executing SQL code within a table field

Posted on 2011-02-23
10
Medium Priority
?
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 41

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 2000 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

719 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