Solved

Executing SQL code within a table field

Posted on 2011-02-23
10
422 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
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…

623 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