March 7, 2008 at 6:32 am
I have a scalar function to return a calculated value. In my table I have a column named LONG_FORMULA which stores a formula for calculating a new value based on dynamic data. Here is an example:
{+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])
After I process the values in the formula I end up with a variable containing the actual values in a statement. example:
+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))
If I perform a select on this I get an answer: 4683.463200
But if I want to return this value from the function it will only allow me to return the statement, not the answer.
How can I get the function to return this answer (4683.463200)? When I do something like this it produces an error: :w00t:
DECLARE @RET_VAL DECIMAL(13,2);
DECLARE @Formula as varchar(255);
-- function does stuff ..
-- resulting in @formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' i use a set here to abbreviate the function
SET @Formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))';
SET @RET_VAL = @Formula;
I get an error: Arithmetic overflow error converting varchar to data type numeric.
March 7, 2008 at 6:58 am
You can use either xml.value (but it is a problem with variables), or you could use dynamic SQL do do this.
DECLARE @RET_VAL DECIMAL(13,2);
DECLARE @Formula AS VARCHAR(255);
-- function does stuff ..
-- resulting in @formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' i use a set here to abbreviate the function
SET @Formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))';
DECLARE @v NVARCHAR(1000)
SELECT @v = 'select ' + @Formula + ' as x'
DECLARE @t TABLE (x DECIMAL(13, 2))
INSERT INTO @t EXEC(@v)
SELECT TOP 1 @RET_VAL = x FROM @t
ps: there is no function to evaluate an arithmetic expression stored in a variable in T-SQL (but you could write one in CLR).
Regards,
Andras
March 7, 2008 at 7:03 am
Sorry, on SQL Server 2000 you cannot use table variables for inserting into, so here is a version with temporary tables:
DECLARE @RET_VAL DECIMAL(13, 2) ;
DECLARE @Formula AS VARCHAR(255) ;
-- function does stuff ..
-- resulting in @formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' i use a set here to abbreviate the function
SET @Formula = '+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))' ;
DECLARE @v NVARCHAR(1000)
SELECT @v = 'select ' + @Formula + ' as x'
--create table #t (x DECIMAL(13, 2))
INSERT INTO #t
EXEC ( @v )
SELECT TOP 1
@RET_VAL = x
FROM #t
DROP TABLE #t
Regards,
Andras
March 7, 2008 at 7:10 am
Thanks for the quick reply.
So in 2000 I should create a temp table and insert to that temp table? Since I am in a function it wont let me create or execute? I could do it outside the function, any ideas?
March 7, 2008 at 7:22 am
You will find that you're going to need to do that outside of a function. Exec isn't allowed in a function.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 7, 2008 at 7:25 am
shogg (3/7/2008)
Thanks for the quick reply.So in 2000 I should create a temp table and insert to that temp table? Since I am in a function it wont let me create or execute? I could do it outside the function, any ideas?
Oops, I missed that you need this in a function 🙁
On 2000 I think you will not be able to do this.
Andras
March 7, 2008 at 7:37 am
Thanks, very helpful - much appreciated! 🙂
April 16, 2008 at 3:20 am
:cool::w00t::Whistling:
November 14, 2008 at 6:39 am
Hi
I am actually busy doing the same thing. I was wondering if you could show me the function that maps the variables to the actual value. It would be very much appreciated
Thanks
November 14, 2008 at 1:53 pm
November 17, 2008 at 12:17 am
Hi
Thanks for the reply but im not quite sure what you mean. I wanted to see the function that takes you from this
{+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])
to this
+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))
dynamically
Thanks
November 17, 2008 at 3:45 am
Hi
Thanks for the reply but im not quite sure what you mean. I was looking fo a function that took you from this
{+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])
to this
+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))
Dynamicaly. For many formulae at once
Thanks
November 17, 2008 at 4:13 am
SELECT ([CONTRACT_PRICE]*PCP)+([FF&E_AMT]*[PCP]*[TAPP_CONTRACT])
FROM TableWithValues
But you're probably asking about writing a translator from one language to another.
If that's the case then I don't see what it has to do with T-SQL.
_____________
Code for TallyGenerator
November 18, 2008 at 12:42 am
Hi
Thanks for the reply but what i wanted to see was the function that takes you from this
{+}([CONTRACT_PRICE]{*}[PCP[){+}([FF&E_AMT]{*}[PCP]{*}[TAPP_CONTRACT])
to this
+((16900.00)*(0.27))+((464.75)*(0.27)*(0.96))
The help would be very much appreciated
Thankyou in advance
November 18, 2008 at 6:04 am
You can say it as many times as you want, it doesn't make any more sense with repetition ;). Please provide sample data and an example explaining what you want, as I don't see why Sergiy's doesn't do what you're asking for. Are you trying to remove your { 's in favor of ( 's?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply