April 6, 2012 at 12:55 am
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.
April 6, 2012 at 2:50 am
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)
April 6, 2012 at 5:27 am
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
April 6, 2012 at 8:41 am
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/%5B/url%5D
_______________________________________________________________
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/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy