This project is offered as an alternative to the builtin ServiceDesk Ticket Search function. The primary difference being the ability to search the Ticket Description.
The following examples explain further:
NOTE: The searched text is enclosed in braces, this signifies that the Description contains a match.
One can expect this project to perform slower than the builtin ServiceDesk Search function.
-- The following index is recommend by the Database Tuning Advisor (Estimated improvement: 46%) /* CREATE NONCLUSTERED INDEX [_dta_index_ReportProcess_Search] ON [dbo].[ReportProcess] ( [SessionID] ASC ) INCLUDE ( [ProcessStarted], [ReportProcessID], [Description], [ProcessTitle]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] */ -- Search Ticket SQL source IF (exists (select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'CmRelatedProcess')) BEGIN with reference ( sessionID, processID ) as ( select rpr.ChildProcessID, rp.ReportProcessID from ReportProcessRelationship rpr with (NOLOCK) join ReportProcess rp with (NOLOCK) on rp.SessionID = rpr.ParentProcessID join CmRelatedProcess crp with (NOLOCK) on crp.ProcessRelationshipId = rpr.ReportProcessRelationshipID ) select rp.ReportProcessID + ISNULL('=>' + reference.processID, '') as [ReportProcessID], rp.ProcessStarted, case when rp.Description like '%' + nullif(@description, '') + '%' then '{ ' + @description + ' } ' + rp.ProcessTitle else rp.ProcessTitle end as [ProcessTitle], rp.SessionID from ReportProcess rp with (NOLOCK) left outer join reference on reference.sessionID = rp.SessionID where rp.ReportProcessID like '%' + nullif(@ticket, '') + '%' or rp.ProcessTitle like '%' + nullif(@title, '') + '%' or rp.Description like '%' + nullif(@description, '') + '%' END ELSE with reference ( sessionID, processID ) as ( select rpr.ChildProcessID, rp.ReportProcessID from ReportProcessRelationship rpr with (NOLOCK) join ReportProcess rp with (NOLOCK) on rp.SessionID = rpr.ParentProcessID ) select rp.ReportProcessID + ISNULL('=>' + reference.processID, '') as [ReportProcessID], rp.ProcessStarted, case when rp.Description like '%' + nullif(@description, '') + '%' then '{ ' + @description + ' } ' + rp.ProcessTitle else rp.ProcessTitle end as [ProcessTitle], rp.SessionID from ReportProcess rp with (NOLOCK) left outer join reference on reference.sessionID = rp.SessionID where rp.ReportProcessID like '%' + nullif(@ticket, '') + '%' or rp.ProcessTitle like '%' + nullif(@title, '') + '%' or rp.Description like '%' + nullif(@description, '') + '%'
There used to be a street named after Chuck Norris, but it was changed because nobody crosses Chuck Norris and lives.