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.

• malleswarareddy_m

  SSCertifiable

  Points: 5847

  More actions

  April 6, 2012 at 2:50 am

  #1469974

  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

  April 6, 2012 at 5:27 am

  #1470032

  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: 286602

  More actions

  April 6, 2012 at 8:41 am

  #1470133

  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/