USE [Vertragsverwaltung_20160404]
GO
/****** Object:  StoredProcedure [dbo].[sp_vertragspartner_search]    Script Date: 02.12.2016 09:08:53 ******/
DROP PROCEDURE [dbo].[sp_vertragspartner_search]
GO
/****** Object:  StoredProcedure [dbo].[sp_vertragspartner_search]    Script Date: 02.12.2016 09:08:55 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE proc  [dbo].[sp_vertragspartner_search] @query varchar(1000),
@fnkt int,
@mitarbeiternr int
as
SET NOCOUNT ON

declare 
	@wordspace 	int,
	@Word		varchar(50),
	@SQLWhere	varchar(4096),
        @scol           varchar(1048)
        
set @scol = 'Vertragspartnernr namez1 namez2 strasse postfach plz ort telefon telefax email internet bemerkung'
set @sqlwhere = ''

if @fnkt=0 begin
   set @sqlwhere = ' namez1 like ' + + char(39) + '%' + @query + '%' + char(39)
   exec('select * from vertragspartner where vertragspartner.securitylevelnr<=dbo.get_securitylevel('+ @mitarbeiternr +') and ' + @sqlwhere + ' order by namez1, namez2')
   return
end 

if @fnkt=1 begin
   set @sqlwhere = ' ort like ' + + char(39) + '%' + @query + '%' + char(39)
   exec('select * from vertragspartner where vertragspartner.securitylevelnr<=dbo.get_securitylevel('+ @mitarbeiternr + ') and ' + @sqlwhere + ' order by namez1, namez2')
   return
end

if @fnkt=2 begin
   exec('select * from vertragspartner where vertragspartner.securitylevelnr<=dbo.get_securitylevel('+ @mitarbeiternr + ') order by namez1, namez2')
   return
end   

   
set @query= REPLACE ( @query , char(39) , '"' )
create table #tmpdeli(item varchar(8000))

if CHARINDEX ( '"' , @query ) > 0
   execute dbo.sp_search_split @query,'"'
else   
   execute dbo.sp_search_split @query,' '

print @query

if @fnkt=2 begin  
   declare xc cursor for select * from #tmpdeli
   open xc
   fetch next from xc into @query
   while @@fetch_status=0 begin
      set @query= REPLACE ( @query , '*' , '%' )
      if len(@sqlwhere) > 0 set @sqlwhere=@sqlwhere + ' and '
      set @sqlwhere = @sqlwhere + 'bkpar00 like ' + char(39) + '%' + @query + '%' + char(39)
      fetch next from xc into @query
   end   
   close xc
   deallocate xc
   drop table #tmpdeli
   print @sqlwhere
   exec('Select * from vertragspartner where '+ @SQLWhere + '  order by namez1, namez2 ')
   return
end




GO