how to write dynamic search query?

Question

how to write dynamic search query?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
August 6, 2012 at 12:04 pm

#265325

for example here i got one dynamic search query how to use or condition in dynamic sql server
DECLARE @WordsToSearch VARCHAR(1000)
DECLARE @Query VARCHAR(2000)
SET @WordsToSearch = 'Mathematics brain'
SET @Query = 'SELECT * FROM PWS_Books WHERE [Description] LIKE ''%' + REPLACE(@WordsToSearch, ' ', '%'' AND [Description] LIKE ''%') + '%'''
PRINT @Query
EXEC(@Query)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

sivag (8/6/2012)
for example here i got one dynamic search query how to use or condition in dynamic sql server
DECLARE @WordsToSearch VARCHAR(1000)
DECLARE @Query VARCHAR(2000)
SET @WordsToSearch = 'Mathematics brain'
SET @Query = 'SELECT * FROM PWS_Books WHERE [Description] LIKE ''%' + REPLACE(@WordsToSearch, ' ', '%'' AND [Description] LIKE ''%') + '%'''
PRINT @Query
EXEC(@Query)

I don't see any reason that you need dynamic sql for this at all.

DECLARE @WordsToSearch VARCHAR(1000)

SET @WordsToSearch = 'Mathematics brain'

SELECT * FROM PWS_Books WHERE [Description] LIKE '%' + REPLACE(@WordsToSearch, ' ', '')

AND [Description] LIKE '%' + 'some other criteria' + '%'

The issue was you had mismatched parenthesis.

You should take a look at this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

In addition to possible sql injection vulnerability, the query you posted is not sargable.

I have a feeling though that you want fulltext searching instead of what you are trying to do here. Am I correct that what you want is any row where either "Mathematics" or "brain" is in the Description? If so, you will have to parse your search string first. This is where the fulltext searching will come in handy.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/