1 Create the project and add below two items.
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
Public Shared Function GetAllNotes(ByVal RefRowPtr As Guid, ByVal seperator As String, ByVal includeSubject As Integer) As <SqlFacet(MaxSize:=-1)> SqlString
Dim Notes As String = ""
Dim sb As StringBuilder = New StringBuilder()
Dim connection As New SqlConnection("context connection=true")
connection.Open()
If String.IsNullOrEmpty(seperator) Then
seperator = seperator.PadRight(20, "-"c)
Else
seperator = seperator.PadRight(20, CChar(seperator))
End If
Using command As New SqlCommand()
command.Connection = connection
command.CommandText = "SELECT ISNULL(ISNULL(sn.NoteDesc, sns.NoteDesc),un.NoteDesc), ISNULL(ISNULL(sn.NoteContent, SNS.NoteContent),un.NoteContent) " & _
"FROM ObjectNotes obj (NOLOCK) " & _
"LEFT OUTER JOIN SpecificNotes sn (NOLOCK) " & _
"ON obj.SpecificNoteToken = sn.SpecificNoteToken " & _
"LEFT OUTER JOIN SystemNotes sns (NOLOCK) " & _
"ON obj.SystemNoteToken = sns.SystemNoteToken " & _
"LEFT OUTER JOIN UserNotes un (NOLOCK) " & _
"ON obj.UserNoteToken = un.UserNoteToken " & _
"WHERE obj.RefRowPointer = '" & RefRowPtr.ToString() & "'"
Try
Dim reader As SqlDataReader
reader = command.ExecuteReader()
Using reader
While reader.Read()
If includeSubject = 1 Then
'Get the note subject
If Len(reader.GetString(0)) > 0 Then
sb.Append(reader.GetString(0) + vbCrLf)
End If
End If
'Get Note content and add note seperator
If Len(reader.GetString(1)) > 0 Then
sb.Append(reader.GetString(1) + vbCrLf + vbCrLf + seperator + vbCrLf)
End If
End While
End Using
Catch ex As SqlException
End Try
End Using
Return sb.ToString()
End Function
End Class
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function SchedDispatchFindAvailDateSp(ByVal iStartDate As DateTime, _
ByVal iMaxDays As Integer, _
ByVal iHrs As Single, _
ByVal iStartTime As SqlDateTime, _
ByVal iEndTime As SqlDateTime, _
<SqlFacet(MaxSize:=2800)> ByRef oInfobar As String, _
ByVal iSessionId As String, _
ByVal iKeepTogether As Boolean, _
<SqlFacet(MaxSize:=10)> ByVal iCustNum As SqlTypes.SqlString, _
ByVal iCustSeq As SqlTypes.SqlInt32 _
) As Integer
Using command As New SqlCommand()
command.Connection = connection
command.CommandText = "GetSchedProfileHoursSp"
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@iScheduleID", DBNull.Value)
command.Parameters.AddWithValue("@iUserName", DBNull.Value)
oParms = command.Parameters.AddWithValue("@oWorkStartTime", iStartTime)
oParms.Direction = ParameterDirection.Output
oParms.DbType = DbType.DateTime
oParms = command.Parameters.AddWithValue("@oWorkEndTime", iEndTime)
oParms.Direction = ParameterDirection.Output
oParms.DbType = DbType.DateTime
oParms = command.Parameters.AddWithValue("@oInfobar", OleDb.OleDbType.Char)
oParms.Direction = ParameterDirection.Output
oParms.DbType = DbType.String
oParms.Size = 2800
oParms = command.Parameters.AddWithValue("RETURN_VALUE", SqlDbType.Int)
oParms.Direction = ParameterDirection.ReturnValue
command.ExecuteNonQuery()
If Not IsDBNull(command.Parameters("@oInfobar").Value) Then
Throw New Exception(CStr(command.Parameters("@oInfobar").Value))
End If
iReturnValue = CInt(command.Parameters("RETURN_VALUE").Value)
If iReturnValue = Severity.Success Then
If iStartTime.IsNull Then
iStartTime = Convert.ToDateTime(command.Parameters("@oWorkStartTime").Value.ToString())
End If
If iEndTime.IsNull Then
iEndTime = Convert.ToDateTime(command.Parameters("@oWorkEndTime").Value.ToString())
End If
End If
End Using
End If
}
2 After compile, run below sql scripts to register assembly.
IF OBJECT_ID(N'GetAllNotes') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.synonyms WHERE name = N'GetAllNotes')
DROP FUNCTION [dbo].[GetAllNotes]
GO
IF OBJECT_ID(N'SchedDispatchFindAvailDateSp') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.synonyms WHERE name = N'SchedDispatchFindAvailDateSp')
DROP PROCEDURE [dbo].[SchedDispatchFindAvailDateSp]
GO
CREATE ASSEMBLY [AppProcedures] FROM
'\\USALVWISMTERM\Debug\AppProcedures.dll'
WITH permission_set = SAFE
ALTER ASSEMBLY AppProcedures
WITH PERMISSION_SET = UNSAFE
GO
IF NOT EXISTS (SELECT 1 FROM sys.synonyms WHERE name = N'GetAllNotes') execute ('
CREATE FUNCTION [dbo].[SSSFSGetAllNotes](@RefRowPtr [uniqueidentifier], @seperator [nvarchar](4000), @includeSubject [int])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SSSFSAppProcedures].[AppProcedures.UserDefinedFunctions].[SSSFSGetAllNotes]
')
GO
IF NOT EXISTS (SELECT 1 FROM sys.synonyms WHERE name = N'SchedDispatchFindAvailDateSp') execute ('
CREATE PROCEDURE [dbo].[SchedDispatchFindAvailDateSp]
@iStartDate [datetime],
@iMaxDays [int],
@iHrs [real],
@iStartTime [datetime],
@iEndTime [datetime],
@oInfobar [nvarchar](2800) OUTPUT,
@iSessionId [nvarchar](4000),
@iKeepTogether [bit],
@iCustNum [nvarchar](20),
@iCustSeq [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SSSFSAppProcedures].[AppProcedures.StoredProcedures].[SchedDispatchFindAvailDateSp]
')
GO
No comments:
Post a Comment