InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 06:48:46 GMT20http://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspxI had to try it for myself so here is your code ported to C#. It worked as advertised for me:[font="Courier New"][size="2"][color="blue"]using [/color][color="black"]System[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data.SqlClient[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]System.Data.SqlTypes[/color][color="gray"];[/color][color="blue"]using [/color][color="black"]Microsoft.SqlServer.Server[/color][color="gray"];[/color][color="blue"]public [/color][color="black"]partial [/color][color="blue"]class [/color][color="black"]UserDefinedFunctions{    [Microsoft.SqlServer.Server.SqlFunction]    [/color][color="blue"]public static [/color][color="black"]SqlString stringEval[/color][color="gray"]([/color][color="blue"]string [/color][color="black"]equation[/color][color="gray"])    [/color][color="black"]{        DataTable table [/color][color="blue"]= new [/color][color="black"]DataTable[/color][color="gray"]();        [/color][color="blue"]object [/color][color="black"]eqObject[/color][color="gray"];        [/color][color="black"]eqObject [/color][color="blue"]= [/color][color="black"]table.Compute[/color][color="gray"]([/color][color="black"]equation[/color][color="gray"], [/color][color="darkred"]""[/color][color="gray"]);        [/color][color="black"]SqlString results [/color][color="blue"]= [/color][color="black"]SqlDecimal.Parse[/color][color="gray"]([/color][color="black"]eqObject.ToString[/color][color="gray"]())[/color][color="black"].ToString[/color][color="gray"]();        [/color][color="blue"]return [/color][color="black"]results[/color][color="gray"];    [/color][color="black"]}}[/color][color="gray"];[/color][/size][/font]Thu, 01 Nov 2012 17:08:41 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspx[quote][b]steve.laforge (11/1/2012)[/b][hr]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![/quote]Yeah, INSERT...EXEC has some restrictions, sorry.You could go back to trying with a SQLCLR object and just issue the [i]SELECT <math>[/i] 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 solutionThu, 01 Nov 2012 10:07:04 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspxAhhhhh! I got it! Sort of...well, okay, it is a work-around anyway.[code="vb"]Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports Microsoft.VisualBasic.StringsPartial 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 FunctionEnd Class[/code]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 [code="sql"]select ..., convert(decimal(18,2), dbo.stringEval(stringWithMathEquation)) ...[/code]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!Thu, 01 Nov 2012 10:06:36 GMTsteve.laforgehttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspxI 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!Thu, 01 Nov 2012 08:31:17 GMTsteve.laforgehttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspx[i]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 [u]never[/u] 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.[/i]Here is one way you could handle it:[code="sql"]DECLARE @v VARCHAR(100); -- local variable for demo, but this could be an stored proc input param tooSET @v = '((7 + 1) / 3.0)'------------------------ the actual code-- setup a SELECT which will force the engine to eval your math problemSET @v = 'SELECT ' + @v;-- setup a table variable to capture your resultDECLARE @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 answerSELECT *FROM @result;[/code]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.Thu, 01 Nov 2012 06:59:37 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspxThank 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.Tue, 30 Oct 2012 18:53:39 GMTsteve.laforgehttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspx[quote][b]steve.laforge (10/30/2012)[/b][hr]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 FunctionEnd ClassI then try to test it with the following query: declare @results decimal(18,2) select @results = dbo.stringEval('(7 + 1) / 3') select @results as resultsWhen 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.[/quote]What do you get whe you execute the following?[code="sql"]declare @results decimal(18,2)select @results = dbo.stringEval('0.0+(7 + 1) / 3')select @results as results[/code]Also, why is someone after you to turn SQL Server into a calculator?Tue, 30 Oct 2012 17:10:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1379043-386-1.aspxI 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 FunctionEnd ClassI then try to test it with the following query: declare @results decimal(18,2) select @results = dbo.stringEval('(7 + 1) / 3') select @results as resultsWhen 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.Tue, 30 Oct 2012 15:04:44 GMTsteve.laforge