SQL Script to use the CADIS search stored procedure

--  Using the CADIS search stored procedure so that you dont have to wait for next page
--
--   Usage 
--
--   Options for table to include
--   set @SearchStr = Set to required search string for a single quote enter 2 
--                    e.g. to search for 'SEDOL'  set @SearchStr = N'''SEDOL'''  
--
-- 
--  Rev     Who  Date   Change
--  01     EDDCUB    First Version (set to 1000 rows output
--
--

declare @searchStr nvarchar(2000)
declare @searchStrLower nvarchar(2000)
declare @searchComponentType int 

declare @Results Table  
(ComponentType nvarchar(200)
,compGUID nchar(500)
,identifier int
,name nvarchar(200)
,DefXml XML
,RowNumber int
,RecordCount int
)

--  Component Types
--  0 DataPorter
--  1 DataInspector
--  2 DataMatcherProcess
--  3 DataMatcherInbox
--  4 DataMatcherRealign
--  5 DataConstructor
--  6 DataIllustrator
--  7 DataIllustratorTemplate
--  8 Solution
-- 11 DataGeneratorFunction
-- 12 DataGeneratorInbox
-- 13 DataManagerFunction
-- 14 DataManagerInbox
-- 15 EventWatcher
-- 16 ProcessLauncher
-- 17 RuleBuilder
-- 18 ManagementDashboard
-- 21 ManagementDashboardItem
-- 22 ManagementDashboardUserGroup
-- 23 Archive
-- 24 Diagram
-- -1   All
--set @searchComponentType = -1    -- All
--set @searchComponentType = 0     -- DataPorter
set @searchComponentType = -1

set @SearchStr = N'''SECTYPE'''

SET @searchStrLower = LOWER(@SearchStr)

INSERT INTO @Results (
 ComponentType
 ,compGUID
 ,identifier
 ,NAME
 ,DefXml
 ,RowNumber
 ,RecordCount
 )
EXEC CADIS_SYS.spSearchComponentXml @SearchTerm = @searchStrLower
 ,@ComponentType = @SearchComponentType
 ,@PageNumber = 1
 ,@RowsPerPage = 1000

SELECT res.*
FROM @Results res
INNER JOIN CADIS_SYS.CO_COMPONENT com ON com.NAME = res.ComponentType
INNER JOIN CADIS_SYS.VW_ALL_PROCESSES OP ON OP.COMPONENTID = com.ID
 AND OP.GUID = res.compGUID
order by rownumber