October 30, 2012 at 3:04 pm
I want to be able to pass a string and have it perform the equation within the string. For example, if I pass it "(7 + 1) / 3)", I owuld expect to get back 2.67.
I have seen several posts on here, but none of them appear to work. So I attempted to create one myself. This is what I have in my stringEval function:
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function stringEval(ByVal equation As String) As SqlDecimal
Dim table As New DataTable
Dim eqObject As Object
eqObject = table.Compute(equation, "")
Dim results As SqlDecimal = SqlDecimal.Parse(eqObject.ToString())
Return results
End Function
End Class
I then try to test it with the following query:
declare @results decimal(18,2)
select @results = dbo.stringEval('(7 + 1) / 3')
select @results as results
When I run this in debug mode in VS2010, I see that the variable 'results' contains 2.6666666667 when it gets to the return statement. However, the query returns '3.00'. I feel like I am so very close to what everyone is asking for, but nobody seems to have an answer for.
Can anyone help me resolve this?
Many thanks!
P.S., yes I know that this is VB instead of C#. I started my effort thinking that I could use the VB Eval function, but then realized that went away with VB.Net.
October 30, 2012 at 5:10 pm
steve.laforge (10/30/2012)
I want to be able to pass a string and have it perform the equation within the string. For example, if I pass it "(7 + 1) / 3)", I owuld expect to get back 2.67.I have seen several posts on here, but none of them appear to work. So I attempted to create one myself. This is what I have in my stringEval function:
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function stringEval(ByVal equation As String) As SqlDecimal
Dim table As New DataTable
Dim eqObject As Object
eqObject = table.Compute(equation, "")
Dim results As SqlDecimal = SqlDecimal.Parse(eqObject.ToString())
Return results
End Function
End Class
I then try to test it with the following query:
declare @results decimal(18,2)
select @results = dbo.stringEval('(7 + 1) / 3')
select @results as results
When I run this in debug mode in VS2010, I see that the variable 'results' contains 2.6666666667 when it gets to the return statement. However, the query returns '3.00'. I feel like I am so very close to what everyone is asking for, but nobody seems to have an answer for.
Can anyone help me resolve this?
Many thanks!
P.S., yes I know that this is VB instead of C#. I started my effort thinking that I could use the VB Eval function, but then realized that went away with VB.Net.
What do you get whe you execute the following?
declare @results decimal(18,2)
select @results = dbo.stringEval('0.0+(7 + 1) / 3')
select @results as results
Also, why is someone after you to turn SQL Server into a calculator?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2012 at 6:53 pm
Thank you, but that did not help. I still get 3.00 as the result.
As for your question of turning SQL Server into a calculator: the application generates a large formula and substitutes values into the string, so I then need to perform the calculations using the actual variables.
November 1, 2012 at 6:59 am
Disclaimer: What I am about to show is dangerous because you're trying to execute a statement passed in by a person or application, and people and applications should never be trusted to do the right thing when it comes to data, so if you use this technique make sure you are protecting against SQL injection.
Here is one way you could handle it:
DECLARE @v-2 VARCHAR(100); -- local variable for demo, but this could be an stored proc input param too
SET @v-2 = '((7 + 1) / 3.0)'
----------------------
-- the actual code
-- setup a SELECT which will force the engine to eval your math problem
-- setup a table variable to capture your result
DECLARE @result TABLE (value VARCHAR(100));
-- execute the SELECT to solve the math problem and cature the result in the table variable
INSERT INTO @result (value)
EXEC (@v);
-- show the answer
SELECT *
FROM @result;
One other note about your original post. SQL Server is forced to infer data types when you only supply a string for calculation meaning unless you provide numbers as decimals (see I changed your 3 to 3.0) then SQL Server will do the math based on integers and not decimals and you will not get the expected result.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 8:31 am
I thought you had it until I tried to add it to a function. Then I get the error 'Invalid use of a side-effection operator 'INSERT EXEC' within a function.'.
I need to be able to use the solution in a function. The idea is that I have built a mathematical string during a query that I actually need to execute and get the results.
Thank you!
November 1, 2012 at 10:06 am
Ahhhhh! I got it! Sort of...well, okay, it is a work-around anyway.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic.Strings
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function stringEval(ByVal equation As String) As SqlString
Dim table As New DataTable
Dim eqObject As Object
eqObject = table.Compute(equation, "")
Dim results As SqlString = SqlDecimal.Parse(eqObject.ToString()).ToString()
Return results
End Function
End Class
I'm sure someone can figure this out, but it actually works better than I had originally thought as is. To use it, I simply call it as
select ..., convert(decimal(18,2), dbo.stringEval(stringWithMathEquation)) ...
In case anyone uses this, or wants to tweak it, I can only get it to work by either using the string that it returns, or to use a convert or cast to decimal. It gets an error if I try to convert/cast it directly to an int. I'm not sure what all of the issues are, but it at least gives me a short-term fix and the result can then be managed with native SQL functions to change it to any other datatype.
Thank you!
November 1, 2012 at 10:07 am
steve.laforge (11/1/2012)
I thought you had it until I tried to add it to a function. Then I get the error 'Invalid use of a side-effection operator 'INSERT EXEC' within a function.'.I need to be able to use the solution in a function. The idea is that I have built a mathematical string during a query that I actually need to execute and get the results.
Thank you!
Yeah, INSERT...EXEC has some restrictions, sorry.
You could go back to trying with a SQLCLR object and just issue the SELECT <math> on the built-in connection back to SQL Server. I doubt it would scale well, but it would not qualify as an INSERT...EXEC and should give you the desired behavior. I do not have time now to test it, but may give it a whirl later.
edit: looks like we were typing at the same time an that you found a good way forward, thanks for posting a solution
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 1, 2012 at 5:08 pm
I had to try it for myself so here is your code ported to C#. It worked as advertised for me:
[font="Courier New"]using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString stringEval(string equation)
{
DataTable table = new DataTable();
object eqObject;
eqObject = table.Compute(equation, "");
SqlString results = SqlDecimal.Parse(eqObject.ToString()).ToString();
return results;
}
};
[/font]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply