locked
Selecting a Parameter using LIKE keyword RRS feed

  • Question

  • I am selecting using teh following Where clause:

    WHERE (SS.statusName = 'Signed Off' OR SS.statusName = 'Closed') AND S.receivedFromTypeID = '1' AND (S.submissionDetails LIKE '%' + @Keyword + '%')
    ORDER BY M.meetingDate, S.agendaNo

    The only parameter that is passed in is for @Keyword, but the LIKE does not seem to be working for this.

    Anyone know why?

    Thursday, January 30, 2014 11:28 AM

Answers

  • Hi JMcCon

    Please modify your condition as below

    select * form tablename where columnname like '%'+@Criteria+'%'

    • Marked as answer by Alisa Tang Tuesday, February 4, 2014 9:12 AM
    Thursday, January 30, 2014 11:49 AM
  • this  (S.submissionDetails LIKE '% @Keyword %')
    should be  (S.submissionDetails LIKE '%' + @Keyword + '%')
    • Marked as answer by Alisa Tang Tuesday, February 4, 2014 9:11 AM
    Thursday, January 30, 2014 8:09 PM
  • I have discovered that there is a problem with the amount of data I was searching and this was causing BIDS to crash.

    The solution that works for me is: (S.submissionDetails LIKE '%' + @Keyword + '%')

    Which was initially posted by me and also suggested by others as the correct solution.

    Thanks for all teh help.

    • Marked as answer by Alisa Tang Tuesday, February 4, 2014 9:11 AM
    Friday, January 31, 2014 10:33 AM

All replies

  • After your LIKE, you open the filter with a '  and then you close it after the wild card.  You're effectively saying LIKE ANYTHING.

    Remove the middle '.

    LIKE '% + @Keyword + %'

    If that fails, remove the +'s:  LIKE '% @Keyward %'

    - If you find my post to be helpful, or the answer, please mark it appropriately.  Thank you.


    Chris Ream



    Thursday, January 30, 2014 11:36 AM
  • the posted statement looks fine to me

    Can you explain what you mean by LIKE doesnt seem working

    Please give an example of value you passed and output you expected for it


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, January 30, 2014 11:48 AM
  • Hi JMcCon

    Please modify your condition as below

    select * form tablename where columnname like '%'+@Criteria+'%'

    • Marked as answer by Alisa Tang Tuesday, February 4, 2014 9:12 AM
    Thursday, January 30, 2014 11:49 AM
  • It does look fine, Can you tell why you think its not working?

    Satheesh
    My Blog


    Thursday, January 30, 2014 11:52 AM
  • Hi, JMcCon

    It look's fine, why u think it's does not working...

    Declare @test nvarchar(10)
    set @test='Abcde'
    select * from table_name where column_name like '%'+@test+'%'

    its working.....


    Thursday, January 30, 2014 12:17 PM
  • None of these have worked.

    If I have ... = @Keyword then this works fine but I want to change it to LIKE instead of = which just won't seem to work.

    Thursday, January 30, 2014 12:45 PM
  • I am passing in a value for the @Keyword parameter and then searching on that value.
    Thursday, January 30, 2014 3:04 PM
  • Can you post the entire code? The piece you posted is having no issues, there must be something else that you are missing

    Satheesh
    My Blog


    Thursday, January 30, 2014 3:10 PM
  • Thanks for the reply Satheesh.

    I am putting a value in the Keyword textbox and then generating the report.

    Here is the entire code that runs in the report:

    SELECT C.Name AS Name, S.subID AS ID, S.subDate As "Date", SV.serviceName As "Service", M.meetingDate As "Meeting Date", M.meetingDetails AS "Meeting Details", DATEPART(YEAR,M.meetingDate) AS "Year", ST.typeName AS Type, SS.statusName AS Status, S.submissionDetails AS Details, ISNULL(A.answerDetails, 'No Answer Entered On System!') AS Answer
    FROM Answer As A RIGHT JOIN Sub AS S ON A.subID = S.subID INNER JOIN SubType AS ST ON ST.typeID = S.subTypeID
    INNER JOIN SubStatus AS SS ON SS.statusID = S.subStatusID INNER JOIN Canal AS C ON C.canalID = S.receivedFromID
    INNER JOIN Meeting AS M ON M.meetingID = S.meetingID INNER JOIN Service AS SV ON SV.serviceID = S.serviceID 
    WHERE (SS.statusName = 'Signed Off' OR SS.statusName = 'Closed') AND S.receivedFromTypeID = '1' AND (S.submissionDetails LIKE '% @Keyword %')
    ORDER BY M.meetingDate, S.agendaNo

    Thursday, January 30, 2014 4:37 PM
  • this  (S.submissionDetails LIKE '% @Keyword %')
    should be  (S.submissionDetails LIKE '%' + @Keyword + '%')
    • Marked as answer by Alisa Tang Tuesday, February 4, 2014 9:11 AM
    Thursday, January 30, 2014 8:09 PM
  • -- Initially posted code
    = '1' AND (S.submissionDetails LIKE '%' + @Keyword + '%')T

    There is a disconnect between what you posted initially and what you posted now.

    --from the current posted code
    (S.submissionDetails LIKE '% @Keyword %')
    The code that you posted initially should work fine

    Satheesh
    My Blog


    Friday, January 31, 2014 4:08 AM
  • I have discovered that there is a problem with the amount of data I was searching and this was causing BIDS to crash.

    The solution that works for me is: (S.submissionDetails LIKE '%' + @Keyword + '%')

    Which was initially posted by me and also suggested by others as the correct solution.

    Thanks for all teh help.

    • Marked as answer by Alisa Tang Tuesday, February 4, 2014 9:11 AM
    Friday, January 31, 2014 10:33 AM