contains column_list as variable

Question

Post reply

contains column_list as variable

bostjan-1030521

Grasshopper

Points: 17
More actions
April 6, 2012 at 12:55 am

#254456

Hi,
I'm having a problem with contains search function for example:
declare
@Column_list varchar(max)
set @Column_list = '(MM_Title, MM_Author, MM_Theme, MM_Description, MM_Publisher, MM_Source, MM_Copyright, MM_Language, MM_Location)'
SELECT *
FROM GEN_L_MM
WHERE CONTAINS(@Column_list, @Search_Input) and MM_Deleted_ID = 0
ORDER BY MM_Title
How can i specify @Column_list as variable?
I need it because user selection of searched fields on the front end of app.

Viewing 4 posts - 1 through 3 (of 3 total)

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

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 1

Hi ,

you can pass variable using dynamic SQL.

declare

@Column_list varchar(max)

set @Column_list = '(MM_Title, MM_Author, MM_Theme, MM_Description, MM_Publisher, MM_Source, MM_Copyright, MM_Language, MM_Location)'

declare @search varchar(20);

set @search='test'

declare @sql varchar(max);

set @sql='SELECT *

FROM GEN_L_MM

WHERE CONTAINS('+@Column_list+','+@search+') and MM_Deleted_ID=0

ORDER BY MM_Title'

exec(@SQL)

or using sp_executesql proc you can pass variables

Malleswarareddy
I.T.Analyst
MCITP(70-451)

bostjan-1030521 Grasshopper Points: 17 More actions · Answer 2

I used this solution, where @Title, @author ... are checkboxes passed to sp.

I wonder which solution is faster.

SELECT *

FROM GEN_L_MM

WHERE

(CONTAINS(MM_Title, @Search_Input) AND @Title = 'true'

or

CONTAINS(MM_Author, @Search_Input) AND @author = 'true'

or

CONTAINS(MM_Author2, @Search_Input) AND @Author2 = 'true'

or

CONTAINS(MM_Theme, @Search_Input) AND @Theme = 'true'

or

CONTAINS(MM_Description, @Search_Input) AND @Description = 'true'

or

CONTAINS(MM_Publisher, @Search_Input) AND @Publisher = 'true'

or

CONTAINS(MM_Source, @Search_Input) AND @Source = 'true'

or

CONTAINS(MM_Copyright, @Search_Input) AND @Copyright = 'true'

or

CONTAINS(MM_Language, @Search_Input) AND @Language = 'true'

or

CONTAINS(MM_Location, @Search_Input) AND @Location = 'true'

or

CONTAINS(MM_Format, @Search_Input) AND @Extension = 'true'

or

CONTAINS(MM_Date_Of_Release, @Search_Input) AND @DateOfRelease = 'true'

or

CONTAINS(MM_IndexedText, @Search_Input) AND @IndexedText = 'true'

)

and MM_Deleted_ID = 0

ORDER BY MM_Title

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

This looks a lot like the classic "catch all" query. Take a look at this article about this type of thing. It will likely perform a lot better than either of the solutions described in this thread.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

_______________________________________________________________

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/