indexes.tsql

CREATE PROCEDURE [dbo].[getResults]
@tableid smallint, 
@query varchar(256)
AS
SET NOCOUNT ON
DECLARE @t_id smallint
DECLARE @name varchar(100)
DECLARE @hidden varchar(100)
DECLARE @sSQL varchar(1000)
DECLARE @strSurname varchar(512)
DECLARE @strFirst varchar(50)
DECLARE @w varchar(100)
declare @col_name varchar(50)
declare @col_type varchar(50)
declare @s varchar(512)
declare @s1 varchar(100)
declare @i smallint
declare @j smallint
declare @copy bit
SELECT @name = (SELECT Name from Indexfields WHERE Name is not null AND Tableid = @tableid )
select @hidden = (SELECT Hidden from Indexfields WHERE Name is not null AND Tableid = @tableid )
if @hidden is null set @hidden=''
set @i = 0
set @copy = 0
set @s = ''
declare col cursor for select column_name, data_type from information_schema.columns where table_name = @name and CHARINDEX(column_name, @hidden ) = 0
open col
fetch next from col into @col_name, @col_type
while @@fetch_status = 0
begin
if @col_name = 'ID' 
set @copy = 1
else 
begin
if @col_type = 'datetime' set @s = @s + ' convert(varchar(11),[' + @col_name + '],13) [' + @col_name + '],'
else if @col_name not in ('Closed') set @s = @s + '[' + @col_name + '],'
set @i = @i + 1
end
if @tableid in (67,82) AND @i > 11 break 
if @tableid in (10,59) AND @i > 10 break
fetch next from col into @col_name, @col_type
end
close col
deallocate col
if @copy = 1 SET @s = @s + 'ID'
else SET @s = LEFT(@s,LEN(@s)-1)
SET @s = 'Select ' + @s + ' FROM [' + @name + '] WHERE '
IF @tableid <> 45 
BEGIN
SET @query = REPLACE(@query,', ',',')
SET @Query = REPLACE(@Query,'''','''''')
SET @strSurname = @Query
END
IF CHARINDEX(':',@query) > 0 – for search form
BEGIN
– SET @s = @s + '['
SET @i = PATINDEX('%and:%',@query)
IF @i > 0
BEGIN
SET @s1 = RIGHT(@query,LEN(@query)- @i - 4)
SET @j = CHARINDEX(';',@s1)
IF @j > 0 
BEGIN
SET @query = LEFT(@query,@i+4) + LEFT(@s1,@j-1)
IF PATINDEX('%date%between%',@query) > 0 SET @query = @query + ''''
SET @query = @query + ' and ' + RIGHT(@s1, LEN(@s1) - @j)
END
END
SET @query = Replace(@query,'+',' ')
SET @query = Replace(@query,'%20',' ')
IF PATINDEX('%date%between%',@query) > 0 
BEGIN
SET @query = Replace(@query,' between]:','] between ''1/1/')
SET @query = Replace(@query,';and:',''' and ''12/31/')
END
ELSE IF PATINDEX('%year between%',@query) > 0 
BEGIN	
/* SET @query = Replace(@query,' between]:','] between ') */
SET @query = Replace(@query,'[year between]:','year between ')
SET @query = Replace(@query,';and:',' and ')
END
IF (CHARINDEX(',',@query) > 0 AND @tableid <> 45)
BEGIN
SET @query = Replace(@query,',',''',''')
SET @query = Replace(@query,':',' IN (''')
IF CHARINDEX(';',@query) > 0 SET @query = Replace(@query,';',''') AND ')
IF PATINDEX('%date%between%',@query) > 0
SET @sSQL = @s + @query + ''''
ELSE IF PATINDEX('%year%between%',@query) > 0
SET @sSQL = @s + @query
ELSE
SET @sSQL = @s + @query + ''')'
END
ELSE
BEGIN
– SET @query = Replace(@query,':','] LIKE ''%')
SET @query = Replace(@query,':',' LIKE ''%')
– SET @query = Replace(@query,';','%'' AND [') 
SET @query = Replace(@query,';','%'' AND ') 
IF PATINDEX('%date%between%',@query) > 0
SET @sSQL = @s + @query + ''''
ELSE IF PATINDEX('%year%between%',@query) > 0
BEGIN
IF PATINDEX('%Remarks%',@query) > 0 
SET @sSQL = @s + @query + '%'''
ELSE IF PATINDEX('%Arriving%',@query) > 0 
SET @sSQL = @s + @query + '%'''
–	SET @sSQL = @s + Replace(@query,'Arriving','[Arriving') + ''''
ELSE
SET @sSQL = @s + @query 
END
ELSE
SET @sSQL = @s + @query + '%'''
END 
SET @sSQL = Replace(@sSQL,'AND '')','')
IF @tableid = 22 
BEGIN
SET @sSQL = Replace(@sSQL,'AND %''','')
IF PATINDEX('%surname %',@sSQL) > 0 
BEGIN
SET @i = PATINDEX('%WHERE%',@sSQL)
SET @s1 = RIGHT(@sSQL,LEN(@sSQL)- @i - 5)
SET @sSQL = LEFT(@sSQL,@i+5) + '((' + @s1 + ') OR ('
SET @sSQL = @sSQL + REPLACE(@s1,'Surname ','Remarks ') + '))' 
END
SET @sSQL = @sSQL + ' AND Reel not like ''%not available%''' 
END
END
ELSE IF CHARINDEX(' ',@query) > 0 OR CHARINDEX('%',@Query) > 0 – for full name search
BEGIN
SET @i = CHARINDEX(' ',@Query)
IF @i = 0 
BEGIN
SET @strSURNAME = @Query
IF CHARINDEX('%',@Query) > 0 SET @strFIRST = '%'
ELSE SET @strFIRST = ''
END
ELSE
BEGIN
SET @strFIRST = LEFT(@Query,@i -1)
SET @strSURNAME = RIGHT(@Query,LEN(@Query)-@i)
END
--SET @i = CHARINDEX(' ',@Query)
--SET @strFIRST = LEFT(@Query,@i -1)
-SET @strSURNAME = RIGHT(@Query,LEN(@Query)@i)
SET @s1 = @strSURNAME + ', ' + @strFIRST
IF @Tableid in (1,43,57) SET @sSQL = @s + ' Names LIKE ''' + @strFIRST + '%' + @strSURNAME + ''''
ELSE IF @Tableid = 7 SET @sSQL = @s + ' Surname LIKE ''' + @strSURNAME + ''' AND Othernames LIKE ''' + @strFIRST + ''''
ELSE IF @Tableid = 22 SET @sSQL = @s + ' Surname LIKE ''' + @strSURNAME + ''' AND Firstname LIKE ''' + @strFIRST + ''' AND Reel not like ''%not available%'''
ELSE IF @Tableid = 32 SET @sSQL = @s + ' School LIKE ''' + @strSURNAME + ''''
ELSE IF @Tableid = 42 SET @sSQL = @s + ' Plaintiff LIKE ''' + @s1 + ''' OR Defendant LIKE ''' + @s1 + ''''	
ELSE IF @Tableid = 48 SET @sSQL = @s + ' [Name of firm] LIKE ''' + @strSURNAME + ''' OR [Person carrying on business] LIKE ''' + @strSURNAME + ''''
ELSE IF @Tableid = 49 SET @sSQL = @s + ' [Ship name] LIKE ''' + @strSURNAME + ''''
ELSE IF @tableid = 64 SET @sSQL = @s + ' Place like ''' + @strFIRST + '%' + @strSURNAME + ''' OR [Description] LIKE ''' + @strFIRST + '%' + @strSURNAME + ''''
ELSE IF @Tableid = 68 SET @sSQL = @s + ' [Applicant (Male)] LIKE ''%' + @Query + '%'' OR [Applicant (Female)] like ''%' + @Query + '%'''
ELSE IF @Tableid = 70 SET @sSQL = @s + '(Surname LIKE ''' + @strSURNAME + ''' Or Remarks like ''%' + @strSurname + '%'') AND Firstname LIKE ''' + @strFIRST + ''''
ELSE SET @sSQL = @s + ' Surname LIKE ''' + @strSURNAME + ''' AND (Firstname like ''' + @strFIRST + ''' OR Firstname like ''' + @strFIRST + ' %'')'
END
ELSE – for multi-surname search
BEGIN	
SET @Query = CHAR(39) + Replace(@Query,',',''',''') + CHAR(39)
--SET @i = CHARINDEX(CHAR(39),@Query)
--IF @i = 0 SET @Query = CHAR(39) + Replace(@Query,',',''',''') + CHAR(39)
IF @Tableid = 7 SET @sSQL = @s + ' Surname IN (' + @Query + ') OR Othernames IN (' + @Query + ')'
ELSE IF @Tableid = 16 SET @sSQL = @s + ' (Surname IN (' + @Query + ') OR Firstname IN (' + @Query + ') OR [Co-Respondent Surname] IN (' + @Query + ') OR [Co-Respondent Firstname] IN (' + @Query + '))'
ELSE IF @Tableid = 22 SET @sSQL = @s + ' (Surname IN (' + @Query + ') OR Firstname IN (' + @Query + ') OR Remarks like ''%' + Replace(@Query,'''','') + '%'') AND Reel not like ''%not available%'''
ELSE IF @Tableid = 32 SET @sSQL = @s + ' Contains(School , ''ISABOUT (' + @strSurname + ')'')'
ELSE IF @Tableid in (1,43,57) SET @sSQL = @s + ' CONTAINS(Names, ''ISABOUT (' + @strSurname + ')'')'
ELSE IF @Tableid = 49 SET @sSQL = @s + ' [Ship name] IN (' + @Query + ')'
ELSE IF @Tableid = 48 SET @sSQL = @s + ' Contains([Name of firm], ''ISABOUT (' + @strSurname + ')'') OR Contains([Person carrying on business], ''ISABOUT (' + @strSurname + ')'')'
ELSE IF @Tableid = 64 SET @sSQL = @s + ' Place IN (' + @Query + ')'
ELSE IF @Tableid = 68 SET @sSQL = @s + ' Contains([Applicant (Male)], ''ISABOUT (' + @strSurname + ')'') OR Contains([Applicant (Female)], ''ISABOUT (' + @strSurname + ')'')'
ELSE IF @Tableid = 70 SET @sSQL = @s + ' (Surname IN (' + @Query + ') OR Firstname IN (' + @Query + ') OR Remarks like ''%' + Replace(@Query,'''','') + '%'')'
ELSE IF @Tableid not in (1,42,43,57) SET @sSQL = @s + ' Surname IN (' + @Query + ') OR Firstname IN (' + @Query + ')'
END	
SET @sSQL = @sSQL + ' ORDER BY '
IF @tableid = 32 SET @sSQL = @sSQL + 'School'
ELSE IF @tableid in (1,43,57) SET @sSQL = @sSQL + 'Names'
ELSE IF @tableid = 42 SET @sSQL = @sSQL + 'Plaintiff'
ELSE IF @tableid = 45 SET @sSQL = @sSQL + 'Item,Reel'
ELSE IF @tableid = 48 SET @sSQL = @sSQL + '[Name of Firm]'
ELSE IF @tableid = 49 SET @sSQL = @sSQL + '[Ship Name]'
ELSE IF @tableid = 7 SET @sSQL = @sSQL + 'Surname,Othernames'
ELSE IF @tableid = 64 SET @sSQL = @sSQL + 'Place'
ELSE IF @tableid = 68 SET @sSQL = @sSQL + '[Applicant (Male)],[Applicant (Female)]'
ELSE SET @sSQL = @sSQL + 'Surname,Firstname' 
SET @sSQL = REPLACE(@sSQL,'[[','[')
SET @sSQL = REPLACE(@sSQL,']]',']')
PRINT @sSQL
EXEC(@sSQL)
GO