Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CLR Eval function Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:47 PM
Points: 4, Visits: 20
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.
Post #1379043
Posted Tuesday, October 30, 2012 5:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1379085
Posted Tuesday, October 30, 2012 6:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:47 PM
Points: 4, Visits: 20
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.
Post #1379117
Posted Thursday, November 1, 2012 6:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
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 VARCHAR(100); -- local variable for demo, but this could be an stored proc input param too
SET @v = '((7 + 1) / 3.0)'

----------------------
-- the actual code

-- setup a SELECT which will force the engine to eval your math problem
SET @v = 'SELECT ' + @v;

-- 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
Post #1379789
Posted Thursday, November 1, 2012 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:47 PM
Points: 4, Visits: 20
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!
Post #1379851
Posted Thursday, November 1, 2012 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 3:47 PM
Points: 4, Visits: 20
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!
Post #1379913
Posted Thursday, November 1, 2012 10:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
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
Post #1379914
Posted Thursday, November 1, 2012 5:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,126, Visits: 12,727
I had to try it for myself so here is your code ported to C#. It worked as advertised for me:

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;
    
}
}
;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1380090
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse