Basically, I created 2 Store Procedures:
1. dbo.usp_SearchUsers {SearchData}
2. dbo.usp_DYN_FilterGenerator {TemplateQuery}, {SearchData}
I didn't say that is the best way, but it is an option. I would like to receive comments.
I read these articles before:
http://www.sql-server-performance.com/transact_sql.asp
http://www.sommarskog.se/dyn-search.html
create proc dbo.usp_SearchUsers
(@request nvarchar(1000))
as
SET NOCOUNT ON
-- SearchUsers 'admin'
declare @statement nvarchar(4000)
declare @filterStatement nvarchar(2000)
declare @data nvarchar(1000)
set @data = rTrim(lTrim(@request))
-- the query
set @statement = N'
SELECT a.aiID, a.CIP, b.FirstName, b.LastName, b.Email,
a.Birthdate, a.Address, a.MobilPhone, a.WorkPhone, a.HomePhone
from dbo.Additional_Information a
INNER JOIN dbo.Users b ON a.aiID = b.UserID
WHERE b.IsSuperUser = 0 AND ({0})>0'
-- template where statement
set @filterStatement = N'
patIndex(''{0}%'',b.FirstName) +
patIndex(''{0}%'',b.LastName) +
patIndex(''{0}%'',b.Email)'
exec dbo.usp_DYN_FilterGenerator @filterStatement OUTPUT, @data
set @statement = replace(@statement,'{0}',@filterStatement)
print len(@statement)
print @statement
print 'Finished.'
exec sys.sp_executesql @statement
end -- end of usp_SearchUsers
-- user sp generator
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_DYN_FilterGenerator]
(@filterTemplate NVARCHAR(2000) OUTPUT, @data NVARCHAR(1000))
as
begin
declare @tempData nvarchar(250)
declare @filter nvarchar(2000)
declare @i int
declare @cachedLen int
set @filter = ''
set @cachedLen = len(@data)
while (@cachedLen > 0)
begin
set @i = charIndex(' ', @data)
if (@i=0)
set @i = @cachedLen + 1
set @tempData = left(@data, @i - 1)
set @data = substring(@data,@i + 1,@cachedLen)
set @cachedLen = len(@data)
if len(@filter) > 0
set @filter = @filter + ' + ' -- ADD version
set @filter = @filter + replace(@filterTemplate,'{0}', @tempData)
end
set @filterTemplate = @filter
end -- end of usp_DYN_FilterGenerator
