Perform calculation In Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Perform calculation In Function

Author

Topic

kalaigowtham
Starting Member

4 Posts

Posted - 2011-08-23 : 03:07:06

DECLARE @TempTable TABLE
(
ID INT
,FD NVARCHAR(400)
,FV FLOAT
)

INSERT INTO @TempTable(ID,FD,FV) VALUES(18,'ABC',20)
INSERT INTO @TempTable(ID,FD,FV) VALUES(18,'BCD',10)
INSERT INTO @TempTable(ID,FD,FV) VALUES(18,'CDE',21)
INSERT INTO @TempTable(ID,FD,FV) VALUES(18,'DEF',11)

DECLARE @FORMULA NVARCHAR(1000)
SET @FORMULA = '(ABC + BCD) / (CDE-DEF) '

DECLARE @ID INT
SET @ID = 18

SELECT @FORMULA = REPLACE(@FORMULA, TT.FD, CONVERT(VARCHAR,CONVERT(DECIMAL(20,4), TT.FV))) FROM @TempTable TT WHERE TT.ID = @ID

select @FORMULA

The Select Will return "(20.0000 + 10.0000) / (21.0000-11.0000)"

How i can get the calculated value of the above statement in a User Defined function.

Finally this User defined function should retun the calculated value 3. Please suggest.

Regards
Kalai

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-23 : 03:10:20

If this function is going to operate on physical table, you can use table-valued function.

Harsh Athalye
http://www.letsgeek.net/

kalaigowtham
Starting Member

4 Posts

Posted - 2011-08-23 : 03:17:12

Unfortunately its a Local Table Variable. This local table values i have generated from the Physical table based on conditions.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2011-08-23 : 03:20:45

In that case you are out of luck. Why do you want to use function for such a simple calculation, anyway?

Harsh Athalye
http://www.letsgeek.net/

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-08-23 : 09:33:40

What you're doing doesn't really make sense. What arguments will this function have? I think it would be helpful to explain what the bigger picture is here and why exactly you want to solve a problem in this way.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-23 : 09:54:19

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163223

Corey

I Has Returned!!

kalaigowtham
Starting Member

4 Posts

Posted - 2011-08-23 : 09:58:58

I have a stored procedure which calls this function for 12 months with number of facility. In this function i have done some calculation for each facility and returning the calculated value back to the procedure.
If you have a better way to solve this problem please help me.

kalaigowtham
Starting Member

4 Posts

Posted - 2011-08-23 : 10:29:55

Corey, It was amazing. I really surprised going through your logic. Thanks for the help. but I have problem here my formula will not be same always. The formula will change and i cannot stick to number of columns since the formula may have N number of fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources