Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: Multiple Keywords in Stored Procedure

    Hi,
    I'm trying to move an asp site from an Access DB to SQL Server.
    The site features a search system which allows multiple keywords to be used in up to three fields.

    The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb...txtCodeId=6701):

    "So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business."

    The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case?

    If it isn't possible, how can I go about this?

    Cheers all.
    __________________________________________________ ___
    Code:
    ASP:
    item=request(item)
    '****item = Replace(item, " ","%' AND item like '%")**** - needs attention
    rs.Open "Exec getlist "& item &"" objConn, 3

    SP:
    CREATE PROCEDURE getlist
    @item nvarchar(255)
    SELECT item FROM publications WHERE item LIKE '%' + @item + '%'
    GO

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    In the expression of item = request("item"), what is the "item"? Is it the value from the text field on the form, in which the user can type the keywords? If yes, the ASP code is

    item = Trim(request("item"))
    If InStr(item, ",") > 0 Then
    item = Replace(item, ",", " ") // replace "," to a space
    End If

    // ......
    // check if some specific characters exist
    // if yes, do it like above

    Do While InStr(item, " ") > 0 // two spaces
    // replace two spaces to one
    item = Replace(item, " ", " ")
    Loop

    // for the keywords matching all of the words, use this one
    item = Replace(Trim(item), " ", "%' AND field LIKE '%")

    // for the keywords matching any of the words use this one
    item = Replace(Trim(item), " ", "%' OR field LIKE '%")

    // pass the variable item to the stored procedure

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    (Note the field is also called "item".)

    This is exactly the problem I described.

    This line:
    item = Replace(Trim(item), " ", "%' AND item LIKE '%")
    Doesn't work.

    In fact, to fit in the SP syntax, this line would have to be:
    item = Replace(Trim(item), " ", "+'%' AND item LIKE '%'+")
    But this doesn't work either.
    Error:

    Server error '80040e14'
    Line 1: Incorrect syntax near '+'

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    The ASP code is okay:

    item = Replace(Trim(item), " ", "%' AND Item LIKE '%")

    But you need to change your SP:

    CREATE PROCEDURE getlist
    @item nvarchar(255)
    AS
    SELECT Item FROM Publications WHERE Item LIKE '%'' + @item + ''%'
    GO
    Last edited by gyuan; 01-10-04 at 23:58.

  5. #5
    Join Date
    Jan 2004
    Posts
    6
    Using the SP:
    [Item] LIKE '%'' + @item + ''%'

    The asp you suggest concatinates into a variable:
    txt1%' AND item LIKE '%txt2

    Gives error:
    Line 1: Incorrect syntax near '%'.

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    When I used the following code,

    Dim item : item = "keyword1 keyword2 keyword3"
    item = Replace(item, " ", "%' AND Item LIKE '%")
    Response.Write item
    Response.End

    I got the following output string:

    keyword1%' AND Item LIKE '%keyword2%' AND Item LIKE '%keyword3

    I did not get any error message.

  7. #7
    Join Date
    Jan 2004
    Posts
    6
    Yes...
    But the error comes from the SP.

    The syntax within the resulting variable is not (as far as I know) syntax that can be used in a SP.

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    Change SELECT statement to

    SELECT Item FROM Publications WHERE Item LIKE '%''' + @item + '''%'

    and add ASP code before call SP

    item = "'" & item & "'"

  9. #9
    Join Date
    Jan 2004
    Posts
    6
    Hi,
    Still no joy.

    In SP:
    item LIKE '%''' + @item + '''%'

    In ASP:
    item = Replace(Trim(item), " ", "%' AND item LIKE '%")
    item = "'" & item & "'"

    Variable becomes:
    'txt1%' AND item LIKE '%txt2'

    Gives error in asp:
    Microsoft OLE DB Provider for SQL Server error '80040e14'
    Incorrect syntax near the keyword 'AND'

    Furthermore, with the SP written like this, no records are returned when a single search term is entered.

  10. #10
    Join Date
    Sep 2003
    Posts
    65
    Hey,

    I created a similar search,

    The code Gyuan has provided seems good.

    Just to verify, you are using Stored Procedures, and not in page SQL statements?

    The only thing I'd do differently, which makes no difference to the outcome, just personal choice, is:

    CREATE PROCEDURE getlist
    @item nvarchar(255)
    AS
    set @item = ('%' + @item + '%');
    SELECT Item FROM Publications WHERE Item LIKE @item
    GO

    reguardless of the format of @item, as long as the column is called Item and exists in the table Publications, that sql shouldn't return an error. If it isn't working correctly, it may never return any results, but it shouldn't error. @item is just a parameter, and shouldn't have any effect on the sql syntax etc, it's contense will just be treated as the search text.

    Perhaps you could paste all the code you are using relating to formatting the search string, and calling the stored procedure (SP) from the database, then also the content of your SP. Then we may be able to help you further.
    -Ashleigh

  11. #11
    Join Date
    Sep 2003
    Posts
    65
    Hey mate,

    I was just eating dinner, and (I'm not sure why) but this problem popped back into my head.

    The issue is, if you are using a stored procedure, the parameters cannot hold additional SQL statements.

    To explain using an example above:

    item = Replace(Trim(item), " ", "%' AND Item LIKE '%")

    But you need to change your SP:

    CREATE PROCEDURE getlist
    @item nvarchar(255)
    AS
    SELECT Item FROM Publications WHERE Item LIKE '%'' + @item + ''%'
    GO

    if @item = fish and Item like frog

    I don't believe it will search for

    SELECT Item FROM Publications WHERE Item LIKE 'fish' and Item like 'frog'

    But I think it will search for
    SELECT Item FROM Publications WHERE Item LIKE 'fish and Item like frog'

    ie: 'fish and Item like frog'

    the '%'' + @item + ''%' statement (with the '') may change this, I am unsure. But from my knowledge, parameters cannot be used to create more complex sql statments unless using EXEC.

    ie

    declare @selectStatement NVARCHAR(255);

    @selectStatement = SELECT Item FROM Publications WHERE Item LIKE %' + @item + '%';

    EXEC @selectStatement;

    That may do what you want, I'm not 100% sure though, I generally stick to using parameters to represent 1 item of data rather than a statement.

    Hope this helps ;-)
    -Ashleigh

  12. #12
    Join Date
    Jan 2004
    Posts
    6
    Hi,
    __________________________________________________ _
    Using SP:
    CREATE PROCEDURE getlisttest
    @item nvarchar(255)
    AS
    SELECT recno, item FROM publications WHERE
    item LIKE '%' + @item + '%'
    GO

    ASP:
    '(no replace system)
    rs.Open "Exec getlisttest "& item &"", objConn, 3

    Results ok on a single search term.
    2 search terms with a space makes the error:
    Server error '80040e14'
    Line 1: Incorrect syntax near 'txt2'.

    **This seems to imply that a simple space in the search term is enough to make an error. I find this surprising.

    Anyhow, to run through other options:
    __________________________________________________ ___
    Using SP:
    CREATE PROCEDURE getlisttest
    @item nvarchar(255)
    AS
    SELECT recno, item FROM publications WHERE
    item LIKE '%'' + @item + ''%'
    GO

    ASP:
    item = Replace(Trim(item), " ", "%' AND Item LIKE '%")
    rs.Open "Exec getlisttest "& item &"", objConn, 3

    No results on a single search term (incorrect).
    2 search terms with a space:
    Variable concatenates to:
    txt1%' AND Item LIKE '%txt2
    Makes the error:
    Server error '80040e14'
    Line 1: Incorrect syntax near '%'.
    __________________________________________________ _______
    Using SP:
    CREATE PROCEDURE getlisttest
    @item nvarchar(255)
    AS
    SELECT recno, item FROM publications WHERE
    item LIKE '%' + @item + '%'
    GO

    ASP:
    item = Replace(Trim(item), " ", "%' AND Item LIKE '%")
    rs.Open "Exec getlisttest "& item &"", objConn, 3

    Results ok on a single search term.
    2 search terms with a space:
    Variable concatenates to:
    txt1%' AND Item LIKE '%txt2
    Makes the error:
    Server error '80040e14'
    Line 1: Incorrect syntax near '%'.
    __________________________________________________ ___

    I think, in fact, that the asp replace line should look like this:
    item = Replace(Trim(item), " ", " + '%' AND Item LIKE '%' + ")
    This concatenates to:
    txt1 + '%' AND Item LIKE '%' + txt2

    But if I use this with the SP line:
    item LIKE '%'' + @item + ''%'
    or
    item LIKE '%'' + @item + ''%'
    or
    item LIKE '%' + @item + '%'

    I get the same error:
    Server error '80040e14'
    Line 1: Incorrect syntax near '+'.
    ---------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------
    ---------------------------------------------------------------------------------------------------

    Ashleigh, you said "The issue is, if you are using a stored procedure, the parameters cannot hold additional SQL statements."
    This is exactly the problem as I originally saw it. I think the above shows this is the case.

    You suggest "parameters cannot be used to create more complex sql statments unless using EXEC."
    I found this page which suggests this is correct:
    http://www.4guysfromrolla.com/webtech/020600-1.shtml

    BUT, I can't even get this code working.

    Please can you give me your thoughts on the following code:

    SP:

    CREATE PROCEDURE getlisttest (@WHEREClause varchar(255))
    AS
    DECLARE @selectStatement NVARCHAR(255)

    SET @selectStatement = "SELECT Item FROM Publications WHERE "
    + @WHEREClause
    EXEC @selectStatement
    GO


    ASP:

    Set objConn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    strDSN = "Provider=SqlOLEDB;Network Library=DBMSSOCN;Data Source=111.111.11.1;Initial Catalog=blah;User Id=blah;Password=blah"
    objConn.Open strDSN

    WHEREClause = Replace(Trim(item), " ", " + '%' AND Item LIKE '%' + ")
    WHEREClause = "Item LIKE '%' + " & WHEREClause & " + '%'"
    WHEREClause = Replace(WHEREClause, "'", "''")

    rs.Open "Exec getlisttest "& WHEREClause &"", objConn, 3


    Using one or two variables:

    ERROR:
    Server error '80040e14'
    Incorrect syntax near the keyword 'LIKE'.


    I'm sure I'm getting close.
    Cheers

  13. #13
    Join Date
    Sep 2003
    Posts
    65
    Hm,

    your stored procedure looks correct to me. So I think the problem is in the string you are sending to it.

    To debug it try this:

    Step 1.
    Instead of sending a string to the SP, just call the SP, and set the whereclause variable inside the SP (to check the SP runs how you would expcet).

    Then, if that doesn't work, play with it til it does (lol).

    Once you have that working, which I think will be right away, you should be able to isolate your problem as being related to the creation of the where string.

    Another thing I am concerned about is how you call the SP.

    You are using asp, not asp.net? If your using ASP, you are probably correct, I never used asp in any detail, so I'm unable to help you with that script.

    Step 2. (well, you could even do this before step 1)
    Ensure the SP call works. Send the SP a simple variable. like a single word. Then make the SP something basic, like, select * from <table> where item like @word

    check the results. If this works,

    Step 3.
    do the same thing, using your SP, but controlling what you send it. Once that works, send it the full string you compiled.

    ---------------

    Running through the problem that way, you should locate the problem. It's a good practice to get into, 9 times out of 10, when I have a problem and I run through it like that, I find I've done a simple typo somewhere which the compilers and parsers didn't detect as it was still 'correct', just illogical, lol.

    Looking at your code again. I put a semi-colon after any statement that isn't the final select statement. But that shouldn't make much difference. SQL is fine (I think)

    If you go through all of this, and it still doesn't work, let me know all your outputs (ie, a few examples of what the trim outputs) etc, then I'll have a play with it in mssql, see if I can work it out. When I made my search though, I created 45 individual variables to hold differing types of data (phrases, and's, or's) then I use them both to select the data, and to determine the percent match. I tend to avoid using the EXEC as it creates a slightly decreased performance, however in your case, it seems the simplest solution, ;-).

    -Ashleigh
    -Ashleigh

  14. #14
    Join Date
    Sep 2003
    Posts
    65
    WHEREClause = Replace(Trim(item), " ", " + '%' AND Item LIKE '%' + ")
    WHEREClause = "Item LIKE '%' + " & WHEREClause & " + '%'"
    WHEREClause = Replace(WHEREClause, "'", "''")

    I don't know how Trim works (never used it), but a few things about that code concern me.

    I am assuming you want:
    % AND Item LIKE %
    inserted between all of the items. And you are defining each item as being 1 word.

    So, could you not use this code:

    WHEREClause = Replace(strSearch, " ", "% AND Item LIKE %")
    WHEREClause = "Item LIKE %" & WHEREClause

    Then, when calling the SP, I'm assuming you have to use EXEC in asp?
    rs.Open "Exec getlisttest "& WHEREClause &"", objConn, 3

    What is the reason for the last 2 quotes? could you just remove them? it's an extra line I know, but I like to do things more formatted, ie

    String strSPCall = "Exec getlisttest " & WHEREClause

    rs.Open strSPCall, objConn, 3
    -Ashleigh

  15. #15
    Join Date
    Sep 2003
    Posts
    39
    You should not try to handle this in ASP. You should handle this in your stored procedure.

    1) Pass all words as one string.
    2) Parse the string, break it up and throw it into an @table using the following algorythm:

    Code:
    -- add your own declarations !!!
    
    while charIndex(',',@pStrKeyword) > 0
    begin
    	select @intSeparatorIndex = charIndex(',',@pStrKeyword)
    	select @strKeyword = substring(@pStrKeyword,1,@intSeparatorIndex-1)
    	insert into
    		@tblKeyword
    	(	keyword
    	)
    		values
    	(	@strKeyword
    	)
    	select @pStrKeyword = substring(@pStrKeyword,@intSeparatorIndex+1, datalength(@pStrKeyword)-@intSeparatorIndex)
    end -- while
    
    insert into
    	@tblKeyword
    (	keyword
    )
    	values
    (	@pStrKeyword)
    )
    3) Query your actual table against the temp table

    Code:
    select
    	something
    from
    	tbWhatever wht (nolock)
    where
    	exists
    		(	select
    				keyword
    			from
    				@tbKeyword kwd
    			where
    				wht.searchField like '%' + kwd.keyword + '%'
    		)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •