#眉標=SQL Server 2005
#副標=SQL Server 2005練功房（2）
#大標=SQL Server 2005整合.Net framework 2.0
#作者=文／楊志強

=====程式=====
程式1
CREATE ASSEMBLY mySQLCLR --組件名稱自訂 
FROM 'C:\AProject\bin\Contacts.dll'  --組件的來源
WITH PERMISSION_SET= EXTERNAL_ACCESS 
--資源存取權限
=====程式=====

=====程式=====
程式2
CREATE PROCEDURE [dbo].[pcSPCLR]
WITH EXECUTE AS CALLER
AS --引用已經註冊的組件
EXTERNAL NAME 
[mySQLCLR].[mySQLCLR.StoredProcedures].
[pcSPCLR]
GO
=====程式=====

=====程式=====
程式3
Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub STP()
   Try --以下是資料庫連線
    Dim cnn As New SqlConnection("context _
connection=true")
    cnn.Open()
    Dim cmd As New SqlCommand
    cmd.CommandText = "select name,type from _
sys.objects"
    cmd.Connection = cnn
    Dim dr As SqlClient.SqlDataReader
    dr = cmd.ExecuteReader() --取得資料
    Dim sp As SqlPipe = SqlContext.Pipe() 
    sp.Send(dr) --進行資料輸出
   Catch ex As Exception
       Dim sp As SqlPipe = SqlContext.Pipe()
       sp.Send("Excption " + ex.Message)
    End Try
    End Sub
End Class
=====程式=====

 =====程式=====
程式4
CREATE FUNCTION FValue(@Number FLOAT) 
RETURNS FLOAT
AS
BEGIN
   DECLARE @rtnValue FLOAT
   IF @Number<=1
     SELECT @rtnValue=1
   ELSE --再一次呼叫自己的函數
     SELECT @rtnValue=@Number *dbo. _
FValue(@Number-1)
   RETURNS @rtnValue
END
GO
=====程式=====

=====程式=====
程式5
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function FValue2(ByVal Number as _
Integer) as Double
       IF Number <=1 then
          Return 1
       Else
          Return Number*FValue2(Number-1)
       End IF
End Function
--部署後執行結果如圖4所示
=====程式=====

=====程式=====
程式6
<Microsoft.SqlServer.Server.SqlFunction
(isdeterministic:=True, isprecise:=True)> _
Public Shared Function RegExMatch(ByVal pattern _
As String, ByVal matchString As String) As Boolean
        Dim r1 As Regex = New _
Regex(pattern.TrimEnd(Nothing))
        Return r1.Match(matchString.TrimEnd(Nothing)).Success
End Function
=====程式=====