Search Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Search Query

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2011-06-01 : 00:51:26

Hi All

I have a scenario to filter the records based on 6 input parameters.

If the values are correct then the record will come, if the value is null then we don't take that field and check with not null fields.

I wrote the query as like below, but it is taken all the records with map only one fields. But my requirement is for ex.. i have 4 parameters a,b,c,d. Here i am given a=null, b='some text', c='some text', d='some text', the records will come based on the above inputs, three are null and d='some text' it will also return records.

CREATEPROCEDURE [dbo].[Lib_SP_BookDetails_SearchTransaction]

@BookTitle Varchar(150)=null,
@Author Varchar(150)=null,
@AccnNo Varchar(50)=null,
@Publisher Varchar(150)=null,
@MediaType Int=0,
@MediaCategory Int=0

AS
BEGIN

SELECT BD.*, MT.MediaType AS 'MediaTypeName', BC.BookCategory AS 'BookCategoryName' FROM Lib_Tbl_BookDetails BD

INNER JOIN Lib_Tbl_MediaType MT ON MT.Id = BD.MediaType

INNER JOIN Lib_Tbl_BookCategory BC ON BC.Id = BD.BookCategory

WHERE PATINDEX('%'+@BookTitle+'%',COALESCE(BD.BookTitle,'') )>0 OR PATINDEX('%'+@Author+'%',COALESCE(BD.Author,'') )>0

OR PATINDEX('%'+@AccnNo+'%',COALESCE(BD.BookCode,'') )>0 OR PATINDEX('%'+@Publisher+'%',COALESCE(BD.Publisher,'') )>0

OR BC.Id = @MediaCategory OR MT.Id = @MediaType AND BD.IsDeleted = 0

END

pls help me to do my requirement.

Thanks,
Kamal

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-06-01 : 09:22:59

You should realy look up "full text indexing". It is created for exactly what you want to achieve.

-> http://msdn.microsoft.com/en-us/library/ms142571.aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-01 : 09:32:05

You can use dynamic sql eg
declare @sql varchar(max)
set @sql='SELECT BD.*, MT.MediaType AS MediaTypeName, BC.BookCategory AS BookCategoryName FROM Lib_Tbl_BookDetails BD

INNER JOIN Lib_Tbl_MediaType MT ON MT.Id = BD.MediaType

INNER JOIN Lib_Tbl_BookCategory BC ON BC.Id = BD.BookCategory'

If @BookTitle is not null OR @Author is not null and
@AccnNo is not null OR
@Publisher Varchar is not null OR
@MediaType Int<>0 OR
@MediaCategory <>0
begin
set @sql = @sql + ' where '
end

If @BookTitle is not null
begin
set @sql = @sql + ' BD.BookTitle like ''%'+@BookTitle+'%'
end

--etc to build up query string
--check string ok
PRINT @sql
exec (@sql)

-sorry had AND instead of OR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources