SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CLR Eval function


CLR Eval function

Author
Message
steve.laforge
steve.laforge
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 21
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84905 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
steve.laforge
steve.laforge
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 21
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.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14835 Visits: 14396
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
steve.laforge
steve.laforge
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 21
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!
steve.laforge
steve.laforge
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 21
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!
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14835 Visits: 14396
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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14835 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search