SQL Server Performance

UDF in sql 2008

Discussion in 'SQL Server 2008 General Developer Questions' started by koese, May 29, 2011.

  1. koese New Member

    Hi I have created the below UDF from SQl CLR database Project from Visiual Studio 2010
    and after diploying the UDF it works fine on the sql test Server

    Since my SQl Production server is a shared hosting server it does not support the .Net Framerwork CLR, It is disabled on the sql 2008 database

    Can some one help me to convert this function into a SQL standalone UDF

    Code:
    Public Shared Function ChecksumMTS(ByVal MerchantxnStem As String) As String
    
    Const ReplaceChars As String = ("abcdef")
    
    Const ReplaceWith As String = ("JKLMPR")
    
    Dim sha_1 As New SHA1CryptoServiceProvider()
    
    Dim i As Long
    
    Dim a As Byte() = Encoding.UTF8.GetBytes(MerchantxnStem)
    
    Dim t As String = Convert.ToBase64String(sha_1.ComputeHash(a))
    
    t = Mid(t, 2, 2)
    
    i = InStr(ReplaceChars, (Left(t, 1)))
    
    If i Then
    
    Mid(t, 1, 1) = Mid(ReplaceWith, i, 1)
    
    End If
    
    i = InStr(ReplaceChars, (Right(t, 1)))
    
    If i Then
    
    Mid(t, 2, 1) = Mid(ReplaceWith, i, 1)
    
    End If
    
    ChecksumMTS = t
    
    End Function
    Thanks
  2. FrankKalis Moderator

    Welcome to the forum!

    Can you please explain what this proc is meant to do? I'm not familiar with .Net and can only guess at the intention of the proc.
  3. koese New Member

    Hi The above procedure should return with 2 extra characters what ever you have supplier intially
    eg: If i send 98A56S47D20
    It will return: 98A56S47D20LM - which are from the above two strings which are declared as ReplaceChars and ReplaceWith.
  4. satya Moderator

    See this link http://msdn.microsoft.com/en-us/library/ms187630.aspx for information on managing the .NET code and UDT, also how frequently you need to run this .NET based code on SQL.
    See books online for the CLR section and also following referred from bOL:
    As with Transact-SQL stored procedures, information may be returned from .NET Framework stored procedures using output parameters. The Transact-SQL DML syntax used for creating .NET Framework stored procedures is the same as that used for creating stored procedures written in Transact-SQL. The corresponding parameter in the implementation code in the .NET Framework class should use a pass-by-reference parameter as the argument. Note that Visual Basic does not suport output parameters in the same way that Visual C# does.

    (I believe this is not a SQL11 or Denali based question so I will move into appropriate section here)

Share This Page