Professional Web Applications Themes

Just a quick SP ? regarding the LIKE SQL keyword - Microsoft SQL / MS SQL Server

Anyone have any ideas why this doesn't return any values? This is an SP that was constructed from a Query in an Access database that was imported into SQL2000. I re-created the query in a stored proc, and this is the first time I have used the LIKE keyword in SQL server and it doesn't seem to work as simply as it did in Access/JET. -- ------------------------------------------------------------------------- -- CREATE PROCEDURE dbo.ADT_sp_ProductSearch_Basic -- Parameter Declaration ( searchCrit char(75) ) -- Begin AS SELECT tblProducts.lngProductID, tblProducts.strSKUnumber, tblProducts.strname, tblProducts.strcategory, tblProducts.strImage, tblKeywords.strKeywords, tblCategory.strCategory FROM (tblCategory INNER JOIN tblProducts ON tblCategory.strCategory = tblProducts.strcategory) INNER JOIN ...

  1. #1

    Default Just a quick SP ? regarding the LIKE SQL keyword

    Anyone have any ideas why this doesn't return any values? This is an SP that
    was constructed from a Query in an Access database that was imported into
    SQL2000. I re-created the query in a stored proc, and this is the first time
    I have used the LIKE keyword in SQL server and it doesn't seem to work as
    simply as it did in Access/JET.

    -- -------------------------------------------------------------------------
    --
    CREATE PROCEDURE dbo.ADT_sp_ProductSearch_Basic

    -- Parameter Declaration

    (
    searchCrit char(75)
    )

    -- Begin

    AS

    SELECT
    tblProducts.lngProductID,
    tblProducts.strSKUnumber,
    tblProducts.strname,
    tblProducts.strcategory,
    tblProducts.strImage,
    tblKeywords.strKeywords,
    tblCategory.strCategory
    FROM
    (tblCategory INNER JOIN tblProducts ON tblCategory.strCategory =
    tblProducts.strcategory)
    INNER JOIN tblKeywords ON tblProducts.lngProductID =
    tblKeywords.lngProductID
    WHERE
    ( tblKeywords.strKeywords LIKE '%' + searchCrit + '%' )
    ORDER BY tblProducts.strSKUnumber;
    -- -------------------------------------------------------------------------
    --

    If I take out the WHERE clause, all the records are returned (4,500 and
    change). Am I not concatenating the variable correctly with the use of a
    wildcard?

    T.I.A.

    Grant Harmeyer


    Grant Harmeyer Guest

  2. #2

    Default Re: Just a quick SP ? regarding the LIKE SQL keyword

    Try changing the searchCrit to varchar(75) as char(75) is going have
    trailing spaces if the length of the string is less than 75.


    "Grant Harmeyer" <gn_harmeyerhotmail.com> wrote in message
    news:Od6fEh%23RDHA.1572TK2MSFTNGP12.phx.gbl...
    > Anyone have any ideas why this doesn't return any values? This is an SP
    that
    > was constructed from a Query in an Access database that was imported into
    > SQL2000. I re-created the query in a stored proc, and this is the first
    time
    > I have used the LIKE keyword in SQL server and it doesn't seem to work as
    > simply as it did in Access/JET.
    >
    > -- -----------------------------------------------------------------------
    --
    > --
    > CREATE PROCEDURE dbo.ADT_sp_ProductSearch_Basic
    >
    > -- Parameter Declaration
    >
    > (
    > searchCrit char(75)
    > )
    >
    > -- Begin
    >
    > AS
    >
    > SELECT
    > tblProducts.lngProductID,
    > tblProducts.strSKUnumber,
    > tblProducts.strname,
    > tblProducts.strcategory,
    > tblProducts.strImage,
    > tblKeywords.strKeywords,
    > tblCategory.strCategory
    > FROM
    > (tblCategory INNER JOIN tblProducts ON tblCategory.strCategory =
    > tblProducts.strcategory)
    > INNER JOIN tblKeywords ON tblProducts.lngProductID =
    > tblKeywords.lngProductID
    > WHERE
    > ( tblKeywords.strKeywords LIKE '%' + searchCrit + '%' )
    > ORDER BY tblProducts.strSKUnumber;
    > -- -----------------------------------------------------------------------
    --
    > --
    >
    > If I take out the WHERE clause, all the records are returned (4,500 and
    > change). Am I not concatenating the variable correctly with the use of a
    > wildcard?
    >
    > T.I.A.
    >
    > Grant Harmeyer
    >
    >

    Prasad Koukuntla Guest

  3. #3

    Default Re: Just a quick SP ? regarding the LIKE SQL keyword

    First, read ISO-11179 about how to name data elements, so you'll stop
    using those confusing, silly, redudant prefixes that make your SQL look
    like 1960's BASIC.

    You did not post DDL or specs, so do you have one keyword per row in the
    Keywords table, or a list of comma separatd values?

    A CHAR(n) is always (n) characters long thanks to blank padding, so your
    parameter probably had a lot of blanks in it. If you are worried about
    any blanks in the string, use REPLACE() instead of trim.

    Try this version:

    CREATE PROCEDURE SearchProducts_Basic
    (searchcrit VARCHAR(75)) -- data type change!!
    AS
    SELECT P1.lng_product_id, P1.sku, P1.name,
    P1.category, P1.image, K1.keywords,
    C1.category
    FROM Categories AS C1,
    Products AS P1,
    Keywords AS K1
    WHERE C1.category = P1.category
    AND P1.lng_product_id = K1.lng_product_id
    AND K1.keyword
    LIKE '%' + LTRIM(RTRIM(searchcrit)) + '%'
    ORDER BY P1.sku;

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

Similar Threads

  1. Help with Keyword search
    By Eric Fortier in forum MySQL
    Replies: 2
    Last Post: February 18th, 03:34 PM
  2. keyword searching
    By pepps in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 21st, 12:42 PM
  3. Keyword Parsing with ASP
    By ARK in forum ASP
    Replies: 19
    Last Post: September 3rd, 10:17 AM
  4. Keyword not supported
    By Carl in forum ASP.NET General
    Replies: 1
    Last Post: August 1st, 04:05 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139