View Thread
 Print Thread
eval function for SQL (CLR)
admin
The following is a CLR that allows the user to create a CLR based EVAL function in SQL - this will evaluate math expressions and return the result as a decimal:

Download Link (includes compiled assembly & source (Visual Studio 2008)):
http://www.heavencore.co.uk/filehub/u...nction.zip

Source: http://stackoverflow.com/questions/10...3#10278603

Main Source:


Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices


Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function EVAL(ByVal InputExpression As SqlString) As SqlDecimal
        Return Evaluate(InputExpression)
    End Function

    Private Shared Function Evaluate(ByVal expression As SqlString) As SqlDecimal
        Dim expressionStr As String = expression.ToString()
        Dim loDataTable = New DataTable()
        Dim loDataColumn = New DataColumn("Eval", GetType(Double), expressionStr)
        loDataTable.Columns.Add(loDataColumn)
        loDataTable.Rows.Add(0)
        Return ParseDecimal(loDataTable.Rows(0)("Eval").ToString(), 0)
    End Function

    Public Shared Function ParseDecimal(ByVal InputStr As String, Optional ByVal ReturnIfFail As Decimal = 0) As Decimal
        Dim ParseOutput As Decimal = 0
        If Decimal.TryParse(InputStr, ParseOutput) = False Then
            Return ReturnIfFail
        Else
            Return ParseOutput
        End If
    End Function
End Class

Jordon Pilling | Heavencore Administrator
 
Jump to Forum